Quarterly data calculation

[pooja]

 :(

[MFGF]

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.

[pooja]

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

[pooja]

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

[MFGF]

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.

[pooja]

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

[MFGF]

Hi Pooja,

What 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.

Have 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?

total ([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.

[pooja]

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

[pooja]

 :( :(

[pooja]

Hi,

Could you please someone help on this?


P

[Francis aka khayman]

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.