Quarterly data calculation

[pooja]

Hi all,

(apologize if this has been already discussed)

I have a report with 3-columns MONTH, QUARTER, YTD with Actual & Budget measures.For input selection I have prompt selection of month/year(01/2014,02/2014...and son on)

So far I am able to calculate and get the data for MONTH & YTD, but fail to get QUARTER data.Below is the example for QUARTER data-

If we select MARCH - then output should be total of (JAN+FEB+MARCH) as Q1
If we select FEB - then output should be total of (JAN+FEB) as Q1

If we select APRIL - then output should be total of (APRIL) as Q2
If we select JUNE - then output should be total of (APRIL+MAY+JUNE) as Q2
and so on ....Q4

Below is what I did but not working. This calculation summing up all the months.

periodsToDate ([CUBE].[IS Period].[IS Period].[Quarter],[SelMonth])

We are using a cube.Please help on this.

P

[MFGF]

Hi all,

(apologize if this has been already discussed)

I have a report with 3-columns MONTH, QUARTER, YTD with Actual & Budget measures.For input selection I have prompt selection of month/year(01/2014,02/2014...and son on)

So far I am able to calculate and get the data for MONTH & YTD, but fail to get QUARTER data.Below is the example for QUARTER data-

If we select MARCH - then output should be total of (JAN+FEB+MARCH) as Q1
If we select FEB - then output should be total of (JAN+FEB) as Q1

If we select APRIL - then output should be total of (APRIL) as Q2
If we select JUNE - then output should be total of (APRIL+MAY+JUNE) as Q2
and so on ....Q4

Below is what I did but not working. This calculation summing up all the months.

periodsToDate ([CUBE].[IS Period].[IS Period].[Quarter],[SelMonth])

We are using a cube.Please help on this.

P

Hi Pooja,

Where are your measures - on the rows?

How are you deriving your Month column? Is it a query calculation with the expression [CUBE].[IS Period].[IS Period].[Month] -> ?YourMonthParam?

Assuming it is, the expression you are using (showwn below) should return a set of month members within the correct quarter. If you use this expression as-is in a column, you should see the relevant months in the quarter displayed as separate column members. Assuming this works, modify the expression to be:

aggregate(currentMeasure within set periodsToDate ([CUBE].[IS Period].[IS Period].[Quarter],[SelMonth]))

This ought to give you what you require.

Cheers!

MF.

[pooja]

Hi MFGF,

Thanks a lot for your prompt response. I tried below functions but still data is not coming out correctly. Below are my response -

Where are your measures - on the rows? - YES
How are you deriving your Month column? Is it a query calculation with the expression [CUBE].[IS Period].[IS Period].[Month] -> ?YourMonthParam? -YES
aggregate(currentMeasure within set periodsToDate ([CUBE].[IS Period].[IS Period].[Quarter],[SelMonth])) - DID NOT WORK????

Let me be more clear on what i am trying to achieve-

Example 1- If I select the FEB on prompt selection then output should be like below:
Month       |    Quarter         |   YTD                |
---------------------------------------------------------
(FEB Data)| (JAN+FEB) Data|(JAN+FEB) Data|

Example 2- If I select the JULY on prompt selection then output should be:

Month       |    Quarter         |   YTD          |
---------------------------------------------------------------------------------
(JULY Data)   | (JULY) Data                 | (JAN+FEB+MARCH+APRIL+MAY+JUNE+JULY) Data|

Hope this makes more clear.

thanks,
P

[promji]

what about you colleacting a quarter data using

aggregate(currentMeasure within set periodsToDate ([CUBE].[IS Period].[IS Period].[Quarter],[SelMonth]))

and in a seperate data item colleacting the selected month ( i.e. your prompt)

the reminder of above 2 should reflect your logic ?>???

thanks

[MFGF]

Hi MFGF,

Thanks a lot for your prompt response. I tried below functions but still data is not coming out correctly. Below are my response -

Where are your measures - on the rows? - YES
How are you deriving your Month column? Is it a query calculation with the expression [CUBE].[IS Period].[IS Period].[Month] -> ?YourMonthParam? -YES
aggregate(currentMeasure within set periodsToDate ([CUBE].[IS Period].[IS Period].[Quarter],[SelMonth])) - DID NOT WORK????

Let me be more clear on what i am trying to achieve-

Example 1- If I select the FEB on prompt selection then output should be like below:
Month       |    Quarter         |   YTD                |
---------------------------------------------------------
(FEB Data)| (JAN+FEB) Data|(JAN+FEB) Data|

Example 2- If I select the JULY on prompt selection then output should be:

Month       |    Quarter         |   YTD          |
---------------------------------------------------------------------------------
(JULY Data)   | (JULY) Data                 | (JAN+FEB+MARCH+APRIL+MAY+JUNE+JULY) Data|

Hope this makes more clear.

thanks,
P

Hi Pooja,

Makes perfect sense, and what I suggested should give you exactly this for your Quarter column.

You said:

aggregate(currentMeasure within set periodsToDate ([CUBE].[IS Period].[IS Period].[Quarter],[SelMonth])) - DID NOT WORK????

I'm afraid my telepathic hat has run out of charge. What does this mean? Did it return an error? Did it return a value but not what you expected - if so what did it return?

Did you try my suggestion of trying it with just the periodsToDate() part to see what months were getting returned? What was the result?

MF.

[pooja]

MFGF,

Sorry for not enough information.
It returns the data but not correct one. I ran the report for APRIL. APRIL is the 1st month of Q2. Instead of displaying APRIL data in QUARTER column it is showing wrong data. So this is the problem.

[MFGF]

MFGF,

Sorry for not enough information.
It returns the data but not correct one. I ran the report for APRIL. APRIL is the 1st month of Q2. Instead of displaying APRIL data in QUARTER column it is showing wrong data. So this is the problem.

So - it doesn't return an error. That's one answer :)
It returns a value but not the right one - that's another answer. :)

Sadly the most fundamental question that would help us figure out what the expression is returning is still unanswered...

If you have simply periodsToDate ([CUBE].[IS Period].[IS Period].[Quarter],[SelMonth]) as the expression for this, what month column(s) get returned by it when you run the report? This should tell us what is going on.

MF.

[pooja]

It look good. But while running for MARCH - it is coming out as JAN FEB MARCH instead sum of them.  >:(

[MFGF]

That is exactly what I hoped for. This proves that the PeriodsToDate() function is returning the correct months, so it's very good news.

Leave that expression intact for the moment, and add another column afterwards that includes the aggregate() syntax around PeriodsToDate() as before. When you run the report now, you should see the three columns for Jan Feb March and the new one that aggregates them. Are the aggregated values in the new column correct - do they match the sum of the measures for each of the three months?

If the answer is yes - try running again when selecting April in the prompt. Do you see just April retuened by the original expression? Does the new expression return the same measure values?

MF.

[pooja]

 ;) ;) ;) ;) ;)

I am testing now. Looks like it is moving on right direction. I will update by EOB today.

[MFGF]

;) ;) ;) ;) ;)

I am testing now. Looks like it is moving on right direction. I will update by EOB today.

Good news! We haven't actually changed anything, though. Or have we? Was it working all along? :)

MF.

[MFGF]

;) ;) ;) ;) ;)

I am testing now. Looks like it is moving on right direction. I will update by EOB today.

Is it fixed? What was the solution?

Cheers!

MF.

[pooja]

MFGF....Thank you so much. sorry for delayed....
It is working fine..this is what I used as per your suggestion-
total([MEASURE] within set periodsToDate ([CUBE].[IS Period].[IS Period].[Quarter],[SelMonth])) and it gives 100% data as needed.  :) :) :) :)

OK. I apologize if another request bother you....!!!


One more information. How to get the rolling this year quarter to last year quarter data from selection [CUBE].[IS Period].[IS Period].[Month] -> ?YourMonthParam?
One scenario-

If we select 04/2013(04 falls under Q2) in prompt selection then output should be
Q2-2013, Q1-2013, Q4-2012, Q3-2012,Q2-2012

............and so on for each random input selection.

Much appreciate your help on this. 

P

[Francis aka khayman]

be sure that Quarter is the parent of month in your time dimension:

[Year]
[Quarter]
[Month]


then use the parent function or ancestor to get the Quarter.... after that you can use periodsToDate or lag,1 lag,2 lag,3 lag,4

[MFGF]

Yep - exactly as khayman says. It's exactly the same technique - just with quarters instead of months. As he indicates, you can get the relevant starting quarter using the parent() function.

MF.

[pooja]

..did not get it exactly....?????

currently I have a prompt for month - [CUBE].[IS Period].[IS Period].[Month] -> ?YourMonthParam?

your suggestion is- I need to modify this month prompt to Quarter??

[CUBE].[IS Period].[IS Period].[Quarter] -> ?YourMonthParam?

Can you please little more details.... >:(

[MFGF]

No, pooja...

You already have the month from your existing prompt. Use this in a parent() function to get the quarter member the month belongs to. Once you have the quarter member you can use the aggregate and periodsToDate functions on this just like last time.

MF.

[pooja]

sorry again...
not sure if I understood correctly.......bit still not working

I will try using quarter prompt instead of month....hope this works... :( :(

[MFGF]

Hi pooja,

Assuming this is the same report, you probably want to continue to use your prompt for Month?

The prompt you have at the moment returns a month member. Assuming your month member belongs to a quarter member in your time hierarchy, all you need to do to isolate the quarter member is to use the parent() function.

eg

Currently your prompt is returning a member into [Selmonth] - correct?

If so, the expression  parent([Selmonth]) will give you the quarter this month belongs to.

Let's call this [Selquarter] shall we?

You can now do this:

lastPeriods(5, [Selquarter])

This will give you the quarter the chosen month belongs to, along with the prior four quarters.

Is this what you need?

MF.

[Francis aka khayman]

i don't think pooja's time dimension is Year, Quarter, Month...

pooja,

what are the other dimensions related to time that you have... also can you give some of the values of your IS Period dimension ([IS Period].[IS Period].[Month]) ??

[pooja]

Thank you  MFGF & khayman.

It is working now. I need to scratch my head more on these dimensional  functions........... ;) ;)

by the way-
how to extract YEAR from date function. Actually DATE (01 2014 format) is coming from 'ParamDisplayValue'...????
From 01 2014  i need to display only 2014 OR just 14.
I tried 'substring' which in not working

[pooja]

MFGF,

lastPeriods(5, [Selquarter]) - this is working fine so far. But one issue - when we select APRIL 2013 which falls under Q2. In this case under Q2 column there should be only APRIL data, not APRIL+MAY+JUNE.  Currently it is summing up for all 3 months falls under Q2.
Another scenario is - when we select FEB 2013 (Q1)then in Q1 columns data should be JAN +FEB as Q1 plus remaining prior Quakers.

Please let me know if this is not clear.....

[Francis aka khayman]

be sure your months level are sorted properly in the model/cube.

[pooja]

I am able to resolve this. Below is what I did-

lastPeriods (4,prevMember ([Data Item1]))

[pooja]

Hi,

Please help me how to below function on prompt value [Selmonth] ->?

[current month_previous year]
[prior_month_current year]]
[current Quater_current year]
[current Quater_prior year]
[previous Quarter_current year]
[YTD_previous year]

Much appreciate your help on this.Thanks.
P.