topic Re: Total in pivot table not correct in QlikView

Total in pivot table not correct

kris_vliegen — Mon, 26 Mar 2018 10:32:49 GMT

Hi All,

I have a Pivot table with 3 dimensions and 1 measure

In the measure (quantity)I have this definition

if(W1TEXT = 'Beginstock', sum({ $ <KeyDatum = {$(vMindate)} >}W1HOEV), if(W1TEXT = 'Eindstock', sum({ $ <KeyDatum = {$(vMaxdate)} >}W1HOEV),sum(W1HOEV))).

The values in the table are correct.

Only the total is the value of sum(W1HOEV) (Quantity Temp)

Who can help me with the correct Total?

Regards,

Kris

Re: Total in pivot table not correct

niclaz79 — Mon, 26 Mar 2018 10:40:32 GMT

Hi,

The pivot table is showing individual values per dimension. The most likely reason you are seeing these is that instances are duplicated (not unique) and is therefore summing up to a higher value.

Re: Total in pivot table not correct

kris_vliegen — Mon, 26 Mar 2018 11:12:36 GMT

Hi,

thanks for your quick support.

And how can I solve this issue?

The total mode is grayed out So I can't sit this to Sum of rows.

Can I do some thing in the expression?

Re: Total in pivot table not correct

parthesh — Mon, 26 Mar 2018 11:26:57 GMT

can you share your app?

Re: Total in pivot table not correct

kris_vliegen — Mon, 26 Mar 2018 12:03:53 GMT

Here is the app.

The total should be correct for every view.

If I only see 3 dimensions it should also be corect if possible

Re: Total in pivot table not correct

sunny_talwar — Mon, 26 Mar 2018 12:08:19 GMT

One option

RangeSum(

Sum({$<KeyDatum = {$(vMindate)}, W1TEXT *= {'Beginstock'}>}W1HOEV),

Sum({$<KeyDatum = {$(vMaxdate)}, W1TEXT *= {'Eindstock'}>}W1HOEV),

Sum({$<W1TEXT -= {'Beginstock', 'Eindstock'}>}W1HOEV))

and other one is to

Sum(Aggr(if(W1TEXT = 'Beginstock', sum({ $ <KeyDatum = {$(vMindate)} >}W1HOEV), if(W1TEXT = 'Eindstock', sum({ $ <KeyDatum = {$(vMaxdate)} >}W1HOEV),sum(W1HOEV))), StationAlles, W1OMAR, W1TEXT, _CalYear, _CalMonth, _CalDay))

http://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/NestedAggregations/sum-of-rows-pivot-tables.htm

Re: Total in pivot table not correct

kris_vliegen — Mon, 26 Mar 2018 12:23:33 GMT

Thanks Sunny,

These both seem to work.

Which should I take?

regards,

Kris

Re: Total in pivot table not correct

sunny_talwar — Mon, 26 Mar 2018 12:24:20 GMT

I would suggest using the 1st option unless you see it causing problems...

Re: Total in pivot table not correct

kris_vliegen — Mon, 26 Mar 2018 12:42:58 GMT

Ok, Thanks for the quick help.