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
Quote from: pooja on 25 Feb 2014 09:26:44 AM
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.
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
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
Quote from: pooja on 25 Feb 2014 11:23:06 AM
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:
Quote from: pooja on 25 Feb 2014 11:23:06 AMaggregate(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.
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.
Quote from: pooja on 25 Feb 2014 11:43:47 AM
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.
It look good. But while running for MARCH - it is coming out as JAN FEB MARCH instead sum of them. >:(
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.
;) ;) ;) ;) ;)
I am testing now. Looks like it is moving on right direction. I will update by EOB today.
Quote from: pooja on 25 Feb 2014 12:10:29 PM
;) ;) ;) ;) ;)
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.
Quote from: pooja on 25 Feb 2014 12:10:29 PM
;) ;) ;) ;) ;)
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.
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
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
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.
..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.... >:(
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.
sorry again...
not sure if I understood correctly.......bit still not working
I will try using quarter prompt instead of month....hope this works... :( :(
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.
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]) ??
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
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.....
be sure your months level are sorted properly in the model/cube.
I am able to resolve this. Below is what I did-
lastPeriods (4,prevMember ([Data Item1]))
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.
:(
Quote from: pooja on 04 Mar 2014 01:27:19 PM
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.
Hi Pooja,
What have you tried already? Where are you getting stuck?
If I was coding this based on [Selmonth] it would be something like:
1. parallelPeriod ([Your Year level],1,[Selmonth])
2. prevMember([Selmonth])
3. parent([Selmonth])
4. parallelPeriod ([Your Year level],1,parent([Selmonth]))
5. prevMember(parent([Selmonth]))
6. aggregate(currentMeasure within set periodsToDate ([Your Year level],parallelPeriod ([Your Year level],1,[Selmonth])))
Looks like I've just written your report for you! Do I get some of your salary now? ;)
MF.
Hi MFGF,
I wish I could share some with you. : ;)
I am working hard to be in better position regarding report development over dimensional modeling. Reason behind keep posting this much question is - running out of time ...deadline. :( :(
I wish I could share details of my current working environment..... :-\ :-\ :-\
I have no special words for all the help/guidance......... !!!!!!
Thank you
P
I have an strange issue I believe-
Is it possible to get total and aggregate value on same column? This is for crosstab report. I have QTD & YTD column in the report with Revenue, Benefits, Expenses & VAR% as rows. Total QTD & YTD data for Revenue, Benefits, Expenses are coming correct. Now VAR% data should be aggregate , but all i am getting is total. Is there anyway to resolve this? I have applied the same logic as MFGF mention in earlier discussion.
P
Quote from: pooja on 12 Mar 2014 08:53:05 AM
I have an strange issue I believe-
Is it possible to get total and aggregate value on same column? This is for crosstab report. I have QTD & YTD column in the report with Revenue, Benefits, Expenses & VAR% as rows. Total QTD & YTD data for Revenue, Benefits, Expenses are coming correct. Now VAR% data should be aggregate , but all i am getting is total. Is there anyway to resolve this? I have applied the same logic as MFGF mention in earlier discussion.
P
Not sure I follow...
Are you saying you have another row below Revenue, Benefits, Expenses and VAR%? Is this what you are referring to? What was the expression you used for the row? Have you tried setting the solve order of the VAR% column to a number higher than 1?
Can you advise?
Cheers!
MF.
MFGF,
These are the rows in order-
Rev
Rev1
Total - Revenue
Outsourcing - 3rd Party
Outsourcing - I/C
Total - Outsourcing
VAR
VAR %[/i]
The problem is with VAR%. when applied total ([ACTUAL] within set periodsToDate ([CUBE].[IS Period].[IS Period].[Quarter],[SelMonth])) as column - all the data coming out as total are good. And this logic does total for VAR% too, but we need aggregate for VAR% not total.
Please let me know if this clear
Hi Pooja,
Quote from: MFGF on 12 Mar 2014 09:49:26 AMWhat was the expression you used for the row?
Here I am referring to the expression you are using to calculate VAR%. You haven't told us what it is.
Quote from: MFGF on 12 Mar 2014 09:49:26 AMHave you tried setting the solve order of the VAR% column to a number higher than 1?
Did you try this? Did it make any difference to the results? If so, what was the difference?
Quote from: pooja on 12 Mar 2014 10:27:47 AMtotal ([ACTUAL] within set periodsToDate ([CUBE].[IS Period].[IS Period].[Quarter],[SelMonth]))
Which column are you using this expression in? The last one that is giving you incorrect results in the VAR% row?
I'd suggest the expression should be:
aggregate(currentMeasure within set periodsToDate ([CUBE].[IS Period].[IS Period].[Quarter],[SelMonth])). Make sure this has a lower solve order than VAR%
I'm still not clear on how your report is coded, so I'm doing a lot of this based on assumptions and guesswork.
MF.
Thank you MFGF,
I am trying to achieve [current quarter_previous year] data based on same ?selectmonth? prompt selection. And this is the expression I have used - parallelPeriod ([Cube].[Quarter],1,[SelMonth]). But this expression did not gave me the data I want.
Could you please help how to get [current quarter_previous year] data?
Thanks
P
:( :(
Hi,
Could you please someone help on this?
P
dont just say "did not give me data i want"
we have no idea if the command execute properly or not. if it did, what did you get? and which data is that in relation to your dimension?
"the data that was returned was current quarter but from 3 years ago." then we have some idea how to help you.