topic Re: Dynamically Change Dimension and Grouping Expression in Cumulative calculation in QlikView

Dynamically Change Dimension and Grouping Expression in Cumulative calculation

Thu, 02 May 2013 04:57:54 GMT

Hi All

I need urgent help on this please:

I have data like below,

Period	Class	Group	No of Intake
Jan	1	VM	5
Jan	2	VM	2
Jan	3	ST	3
Jan	4	ST	3
Feb	1	VM	2
Feb	2	VM	2
Feb	3	ST	1
Feb	4	ST	5
Mar	1	VM	3
Mar	2	VM	1
Mar	3	ST	1
Mar	4	ST	3
Apr	1	DC	2
Apr	2	DC	2
Apr	3	DC	1
Apr	4	LP	2
Apr	5	LP	3

Now, In Jan Yearly Total Intake by Group Report Should Show,

Group	YTD Intake
VM	7
ST	6

Because, VM total = Class 1 + Class 2 and ST Total = Class 3 + Class 4

Similarly,

Feb Report will be :

Group	YTD Intake
VM	11
ST	12

Mar Report will be:

Group	YTD Intake
VM	15
ST	16

But interesting will be the report of Apr, where re grouping caused, Class 1, 2 & 3 to be under DC and Class 4 under LP and a new class introduced which is class 5 and that also is under LP.

Now for Apr report, YTD Total for DC will be = YTD total of Class 1 + YTD Total of Class 2 + YTD Total of Class 3; Similarly LP Total = YTD Total of Class 4 + YTD Total of Class 5;

And the report should be as:

Group	YTD Intake
DC	25
LP	14

I've no Clue how to display this. Would you kindly help?

Re: Dynamically Change Dimension and Grouping Expression in Cumulative calculation

er_mohit — Thu, 02 May 2013 06:54:16 GMT

hii

see the attached file

in LP i count its 16 not 14

Re: Dynamically Change Dimension and Grouping Expression in Cumulative calculation

Gysbert_Wassenaar — Thu, 02 May 2013 09:36:51 GMT

See attached example.

Re: Dynamically Change Dimension and Grouping Expression in Cumulative calculation

Fri, 03 May 2013 01:00:21 GMT

Thank You so much... Your answer is really very helpful. May I please ask (as the requirement is changing a little)

how to generate the report based on the range of Periods selected; For example If from Period List box, I select Feb Mar & Apr then report should exclude Jan Valuse but will show result as per latest Group Structure?

Feb + Mar + Apr ::

Group	Total Intake
DC	15
LP	13

Same way, if say I select Jan and Feb: then result should be::

Group	Total Intake
VM	13
ST	12

Re: Dynamically Change Dimension and Grouping Expression in Cumulative calculation

Fri, 03 May 2013 01:00:46 GMT

Thank You so much... Your answer is really very helpful. May I please ask (as the requirement is changing a little)

Feb + Mar + Apr ::

Group	Total Intake
DC	15
LP	13

Same way, if say I select Jan and Feb: then result should be::

Group	Total Intake
VM	13
ST	12