Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a requirement to calculate the targets for the current year based on last year december actuals. The calculated targets needs to be shown against the current year actuals in a pivot table.
I have written a simple set analysis for this and it is not giving me the results I am expecting.
Attached is the sample QVW with the issue I am having.
Could you please help me?
Regards,
Murali
Like this?
Your expression should be
=SUM(TOTAL {<Period = {"Dec-2013"}>}Actuals)-(SUM(TOTAL {<Period = {"Dec-2013"}>}Actuals)*Aggr(Reduction%,Period))
Manish..
Thanks for the quick response. It almost solved my issues but had issues when I added another column Type to both tables.
The target is calculating correctly when I select a value in the Type list box but when no value is selected it is showing blank target.
When no Type value is selected, it should pick Dec-2013 value for Type A and apply Type A reduction % + pick Dec-2013 value for Type B and apply Type B reduction %.
Attached is the sample QVW.
could you pelase help?
Like this?
UPDATE: previous reply and file both wrong...
change your expression as below..
=SUM(TOTAL <Type>{<Period = {"Dec-2013"}>}Actuals)--(SUM(TOTAL {<Period = {"Dec-2013"}>}Actuals)*Aggr(Reduction%,Period,Type))
The issue is, I can't have Type added as a dimension in my Pivot.
Is there a way to achieve this without adding 'Type' as a dimension?
Change expression as below.... and remove Type from dimension.
IF(GetSelectedCount(Type) = 0,SUM(TOTAL {<Period = {"Dec-2013"}, Type = {'A'}>}Actuals)-SUM(TOTAL {<Period = {"Dec-2013"}, Type = {'A'}>}Actuals)*Only({<Type = {'A'}>}Reduction%),
SUM(TOTAL {<Period = {"Dec-2013"}>}Actuals)-SUM(TOTAL {<Period = {"Dec-2013"}>}Actuals)*Reduction%)
The below are the issues I am facing with this approach...
1. We might have more than 2 distinct values under the field 'Type'.
2. Even when we have only 2 distinct values, when I use this expression with real data I have in my production version (around 15 million rows), the performance is not good.
3. I tried to calculate this during script time, but the actuals change when user apply filters on the dashboard and hence I need this target calculation to be dynamic.
Hi,
Please refer the attached...
When no value is selected for Division, the pivot is not showing the data. But when a value is selected is working fine.
The requirement is that it should work in both the cases.
Thanks in advance for your help.
Regards,
Murali