topic Re: Total in Pivot table for YTD?? in QlikView

Total in Pivot table for YTD??

narender123 — Thu, 07 Jul 2016 10:45:07 GMT

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

Re: Total in Pivot table for YTD??

sunny_talwar — Thu, 07 Jul 2016 11:07:25 GMT

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

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

Re: Total in Pivot table for YTD??

Thu, 07 Jul 2016 11:08:34 GMT

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.

Re: Total in Pivot table for YTD??

narender123 — Thu, 07 Jul 2016 11:33:41 GMT

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.

Re: Total in Pivot table for YTD??

narender123 — Thu, 07 Jul 2016 11:36:59 GMT

No this is the different scenario.

Thanks,

Re: Total in Pivot table for YTD??

narender123 — Thu, 07 Jul 2016 11:37:52 GMT

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.

Re: Total in Pivot table for YTD??

sunny_talwar — Thu, 07 Jul 2016 12:13:40 GMT

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

Re: Total in Pivot table for YTD??

narender123 — Thu, 07 Jul 2016 13:15:38 GMT

No.Still not showing right.

Re: Total in Pivot table for YTD??

sunny_talwar — Thu, 07 Jul 2016 13:19:28 GMT

Would you be able to share a sample?