Dynamically Change Dimension and Grouping Expression in Cumulative calculation

[#1]

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?

[er_mohit]

hii

see the attached file

in LP i count its 16 not 14

[Gysbert_Wassenaar]

See attached example.

[#4]

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

[#5]

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