topic Re: Re: Dynamic target calculation in QlikView

Dynamic target calculation

Wed, 26 Mar 2014 09:28:23 GMT

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

Re: Dynamic target calculation

MK_QSL — Wed, 26 Mar 2014 09:44:17 GMT

Like this?

Your expression should be

=SUM(TOTAL {<Period = {"Dec-2013"}>}Actuals)-(SUM(TOTAL {<Period = {"Dec-2013"}>}Actuals)*Aggr(Reduction%,Period))

Re: Re: Dynamic target calculation

Wed, 26 Mar 2014 11:19:45 GMT

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?

Re: Dynamic target calculation

MK_QSL — Wed, 26 Mar 2014 11:26:45 GMT

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))

Re: Dynamic target calculation

Wed, 26 Mar 2014 11:36:01 GMT

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?

Re: Dynamic target calculation

MK_QSL — Wed, 26 Mar 2014 11:47:48 GMT

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%)

Re: Dynamic target calculation

Wed, 26 Mar 2014 12:25:54 GMT

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.

Re: Re: Dynamic target calculation

Tue, 01 Apr 2014 14:18:01 GMT

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