Year to Date Measure

[system]

Can anyone please suggest me the syntax for creating YTD measure in BO universe?

I have Fiscal Yr, fiscal Month, Monthenddate and Sales columns in the table?

Database----SQL server

Thanks in advance
Kala

---

david1 (BOB member since 2010-11-23)

[system]

Please serach the forum.

I suggest you to search the forum before posting the questions.

---

upendra_boc (BOB member since 2010-09-06)

[system]

Check this sticky topic
https://bobj-board.org/t/152613

---

Mak 1 (BOB member since 2005-01-06)

[system]

@Mak,
Thanks …But those functions are not available in the universe.I am getting parse errors.

Can u please suggest any alternate syntax

Dbase—SQL server 2007
BO XI 3.1

---

david1 (BOB member since 2010-11-23)

[system]

You have to reference your fiscal year column, it won’t work otherwise :).

Sum(Case When TableName.FiscalYear = dateadd(yy, datediff(yy,0,getdate()), 0) Then Tablename.SalesAmount Else 0)

On another note, objects that don’t parse will work with others in an actual query, try it and see 8) .

---

Mak 1 (BOB member since 2005-01-06)

[system]

@ Mak,

I did that…but no luck.Even the query shoots error messsage…
Any other possibility?

can i use
convert (SMALLDATETIME, {fn CURDATE()}) …I dont know its function?

---

david1 (BOB member since 2010-11-23)

[system]

@ Mak, I used the same code,but the data is showing for the current month,
Its not summing up…

What is the alternate solution?

Thanks

---

david1 (BOB member since 2010-11-23)

[system]

So, what code did you use?
The one given is comparing fiscal year with the current year? Used with just year object, it should work.
You will have to create other measures for Fiscal month and month End Date.
Please refer to the link I gave earlier and apply the logic in a similar way, if your YTD works.

---

Mak 1 (BOB member since 2005-01-06)

[system]

I used the following code:

Sum(Case When TableName.FiscalYear = dateadd(yy, datediff(yy,0,getdate()), 0) Then Tablename.SalesAmount Else 0),

---

david1 (BOB member since 2010-11-23)

[system]

Well, what do you get when you use that, just with your fiscal year object?

---

Mak 1 (BOB member since 2005-01-06)

[system]

I got the sale amount for selected month only…

Eg:
For month of Feb ,
1)The sales amount and
2)YTD sales amount(JAN+FEB) should be displayed,

I used the above code for (2) by refering fiscal yr column …But it is not adding the sales for jan & Feb…

---

david1 (BOB member since 2010-11-23)

[system]

Please post the SQL used in the query, from the query panel.

---

Mak 1 (BOB member since 2005-01-06)

[system]

SELECT
  Legacy_Sales.dbo.CustomerProfitabilitySummary.PLGroup,
  Legacy_Sales.dbo.CustomerProfitabilitySummary.Amount,
  Legacy_Sales.dbo.CustomerProfitabilitySummary.BudgetAmount,
  Sum(case when ( Legacy_Sales.dbo.CustomerProfitabilitySummary.FiscalYr ) = dateadd(yy,datediff(yy,0,getdate()),0) then Legacy_Sales.dbo.CustomerProfitabilitySummary.Amount else 0 End)
FROM
  Legacy_Sales.dbo.CustomerProfitabilitySummary
WHERE
  Legacy_Sales.dbo.CustomerProfitabilitySummary.FiscalMnth  IN  ( '02'  )
GROUP BY
  Legacy_Sales.dbo.CustomerProfitabilitySummary.PLGroup, 
  Legacy_Sales.dbo.CustomerProfitabilitySummary.Amount, 
  Legacy_Sales.dbo.CustomerProfitabilitySummary.BudgetAmount

The following is the syntax for object created in universe:

Sum(case when @Select(Measures\Fiscal yr) = dateadd(yy, datediff(yy,0,getdate()), 0) then Legacy_Sales.dbo.CustomerProfitabilitySummary.Amount else 0 End)

[Moderator Edit: Added code formatting - Andreas]

---

david1 (BOB member since 2010-11-23)

[system]

Think about what you are doing

Legacy_Sales.dbo.CustomerProfitabilitySummary.FiscalMnth IN ( '02' ) 

Will just bring you back February for all fiscal years, put your Fiscal month and Year in the Select and you will see what you are fetching.
Your filter means my code won’t work :).

---

Mak 1 (BOB member since 2005-01-06)

[system]

Let me explain you scenario:

I need to prepare a report for income statement (ACTUAL &BUDGET) for the year 2010.
I have the following columns in my database:Fiscal Yr,Fiscal Month,sales amount.
I need to create objects for YTD and MTD sales revenues for all the Productline groups(Eg: Gross sales,Gross profit).For this,
I need to create an object in universe such that if the user (for eg: ) want to see the budget for gross sales for Feb2010…he/she should see the gross sales for Feb & YTD sales for FEB(Sum of sales revenue from start date of fiscalyear;i.e;JAN+ FEB).

I was confused in creating object for YTD measure…

If you understand the context,Please help me out…

Let me know…if it is still ambiguous…

---

david1 (BOB member since 2010-11-23)

[system]

Santhikala,

please check this topic…

Need Formula for Week, WTD, MTD, YTD

Hi Experts, I am having some reports, In which I need to display Day, Week, Month, Week to Date, Month to Date, Year to Date. I am using BOXiR2, Oracle 9i as back end. I have searched entire forum, But I did not the proper solution to my requirement. Please let me know the logic. NOTE: I don’t have these columns in the DB, I need to create it in the Universe level. Table Name: CUSTOMER Coulumn Name: DATE Based on the user selection in the report level, I need to display the report in Cr…

Accordingly implement this in SQl Server.

---

vasu237 (BOB member since 2007-02-19)

[system]

I think there are 2 options to do it.

1. Option 1
   Create the YTD object in report level. Formula is runningsum().

2. Option 2
   Create an alias table for that fact table (table that consists the measure eg Budget_YTD).

Both fact tables need to join to a time dimension table eg Dim_Financial.Period. (YYYYMM format)

The joining conidtion is will be something like this
SUBSTRING(Budget_YTD.PERIOD,1,4)=SUBSTRING(CONVERT(VARCHAR,DIM_FINANCIAL.PERIOD),1,4) AND
SUBSTRING(CONVERT(VARCHAR,DIM_FINANCIAL.PERIOD),5,2)>=SUBSTRING(BUDGET_YTD.PERIOD,5,2)

Final step is to define the contexts.

Im always use the second option, hope this help. Or anyone have a better solution?

---

ylai20 (BOB member since 2008-05-21)