Prior Year Cumulative measure giving me Actual year Amount - Help

[Former Member]

I get Actual Amount for Prior Year Selected. Please see attached files for my Output, Queries, Measures used and selection. THanks for all you help. I need to make Cumulative - Prior Year Balance GL measure show as Prior year amount but its giving me the same output as Actual Year

[Former Member]

Have a look at the SQL generated and see if the measure value that is causing you problems would logically return previous year or current year transactions. If you have any doubts, raise them with the universe designer as they will know your objects better than we do.

The construct for a prior year object should be a case statement along the lines of (in pseudo code terms):

sum(case when year(transaction_date) = year(currentdate)-1 then transaction_value else 0 end)

The other way to achieve this is through a smart calendar table, such that the calendar dimension gets updated each night to denote certain things - last week flag, yesterday flag, etc In your case, there would be a prior year to date 1/0 flag and prior year to date would simply be:

sum(transaction_value * prior ytd flag)

[tanveer1]

Hi,

Create a new varaible Cumulative Prior Year = previous([Cumulative - Actual Year]) and use this in thae table.

Thanks,

Tanveer

[Former Member]

Thank you Tanveer, I used the formula and it still gives me the actual year amount too and not previous year. I need the previous amount. All help appreciated. Thanks

[Former Member]

If you want to have previous year data then you have to bring previous year data in separate query and then use the same to build the report.

[former_member211235]

Hi,

Create another provider which brings all the years data without any filters on year in the 2nd provider. Now merge the year objects and create a variable which shows only prev year and use this variable in your calculation.

Grtz

-Anila.

[Former Member]

Thank you Swapnil, I actually did that as well and merged objects but still giving me actual amount. Am confused. I need help please. Or maybe am missing something?

[Former Member]

In query where you are fetching previous data you have applied any date condition like

let's say in current query you are fetching data for May month now in case of previous month you have to apply condition where month = April where you are fetching data for previous month.

I hope you understand what I mean.

Beacuse I am not able to see any condition in either of your queries.

[Former Member]

Anila, thank you for your input, I somehow mised your message earlier. What do you mean in part of your last sentence "use this variable in your calculation"  which variable you referring too I should use?

Swapnil, I understand what you want me to do but I want the prompt option and not having to enter Months in query panel. Appreciate all ur help please.

[former_member211235]

I mean to use the prev year variable for calculating the amount in prev year.

=[Amount] in ([Prev Year])

For [Prev Year]= ToNumber(UserResponse("Enter a Year");" ") -1

Hope it helps ...!!

Grtz

-Anila.

[Former Member]

Thanks Anila, Am confused with your formula.

My Actual Year Measure is "Cumulative - Actual Year". Can you please refine your formula for me please am confused. Appreciate this.

=[Amount] in ([Prev Year])   -   I do not understand this (is the amount supposed to be my actual measure or prior measure?

For [Prev Year]= ToNumber(UserResponse("Enter a Year");" ") -1         (I beleive this is to give user response of period selected?

[former_member211235]

Yes. It is the actual amount measure to be used in place of [Amount].

For  [Prev Year]= ToNumber(UserResponse("Enter a Year");" ") -1, just create a variable using this formula and verify whether you are able to fetch prev year.Merge year objects. Use this prev year variable in the calculation of amount variable and check whether you are getting expected results for prev year.

Grtz

-Anila.