Total in Pivot table for YTD??

[narender123]

Hi All,

I have a table with Department ,Branch, Year, Month, YTD_Amount.

YTD Amount is like

1st month is 100 then 2nd month is 200 then 3rd month 500

then 4th month 600 -------- 12th month 1700 means accumulated.

I have created a report which is showing sum(YTD_Amount)

like below and its right result.

Department      Branch        Year      2015

                                          Month     1             2             3             4    ---------- 12

A                       testing                    100       200            500         600               1700

Total                                                100       200            300         600              1700

But when i am not expend symbol in Pivot table for Year and Month

then total result shows wrong data like:-

Department      Branch          Year      + 2015

                                            Month   

A                       testing                       2900

Total                                                   2900

Which is wrong because this is my Year to Date amount so my total amount should be equal to max(month) of each year like in table 12th month of 2015 will be 1700 not 2900. So how to show total data correct for YTD amount

Please suggest me to sort out this.

Narender

[sunny_talwar]

Try something like this if you YTD_Amount only increases each period (no negatives)

=If(SecondaryDimensionality() = 2, Sum(YTD_Amount), Max(YTD_Amount))

[#3]

The following could assist you in resolving your problem:

QlikView App: Simple Year To Date Example (Set Analysis)

Using the outlined concepts should get you the desired results.

[narender123]

Hi Sunny,

By mistake , I have clicked Assumed Answer.

I have checked your expression and so the same in my report, But in my report its not working.

Please see my screen shots.

I have used same expression as you suggested.

[narender123]

No this is the different scenario.

Thanks,

[narender123]

Hi Sunny,

By mistake , I have clicked Assumed Answer.

I have checked your expression and so the same in my report, But in my report its not working.

Please see my screen shots.

I have used same expression as you suggested.

[sunny_talwar]

How about this:

=If(SecondaryDimensionality() <> 2, Max(YTD_Amount), Sum(YTD_Amount))

Changed the if statement to check if the SecondaryDimensionality is not equal to 2 and changed the order of the two expressions

[narender123]

No.Still not showing right.

[sunny_talwar]

Would you be able to share a sample?