topic Re: PivotTable - Sum Error in QlikView

PivotTable - Sum Error

luiz_damascena — Wed, 25 Nov 2020 16:16:04 GMT

I have a dynamic table, with three dimensions (organ, judge, cabinet) and eight expressions (Distributed, Redistributed, Restituted, Net Dist, In Session, Monocratic, Solved, Julg Rate and Missing).

Distributed is "= Count ({<num_item_egestao = {2137,92137,2138,92138,2139,92139}"} process_gestion) "

Redistributed is "= Count ({<num_item_egestao = {2142,92142}>} process_egestao)"

Restituted is "= Count ({<num_item_egestao = {2152,92152}>} process_egestao)"

Net Dist is "Distributed + Redistributed - Restituted"

In Session is "= Count ({<num_item_egestao = {2192,92192}>} process_egestao)"

Monocratic is "= Count ({<num_item_egestao = {2195,92195}>} process_egestao)"

Solved is "" In Session "+ Monocratic"

Rate Julg is "if (" Net Dist "> 0, (Solved /" Net Dist "), 0)"

Missing is "if (" Net Dist "> Solved," Net Dist "- Solved, 0)"

My problem is in the Missing column. Actually, the sum of it.

By hypothesis,

line 1: Net Dist = 2, Solved = 0, Missing = 2

line 2: Net Dist = 134, Solved = 88, Missing = 46

line 3: Net Dist = 0, Solved = 1, Missing = 0 (Solved> Net Dist, result 0.)

The "Show Subtotals" is marked for all three dimensions.

The sum should be 48, but the result is 47!

It is not adding up the Missing column. He's recalculating!

Net Dist = 136 (2 + 134 + 0), Solved 89 (0 + 88 + 1). 47!

But I need to add the Missing column (and do not recalculate the row), because there is a formula in it - when negative, 0 is assigned as a result.

How to solve?

Thanks for any help or clarification.

Re: PivotTable - Sum Error

sunny_talwar — Fri, 02 Mar 2018 14:36:31 GMT

Try this

Sum(Aggr(

RangeMax(

RangeSum(

Count({<num_item_egestao = {2137,92137,2138,92138,2139,92139}"} process_gestion),

Count ({<num_item_egestao = {2142,92142}>} process_egestao),

-Count ({<num_item_egestao = {2152,92152}>} process_egestao),

-Count ({<num_item_egestao = {2192,92192}>} process_egestao),

-Count ({<num_item_egestao = {2195,92195}>} process_egestao)

, organ, judge, cabinet))

Re: PivotTable - Sum Error

luiz_damascena — Fri, 02 Mar 2018 14:54:15 GMT

The Missing column on all rows has become zero, now...

Re: PivotTable - Sum Error

sunny_talwar — Fri, 02 Mar 2018 14:57:46 GMT

My bad, try this

Sum(Aggr(

RangeMax(

RangeSum(

Count({<num_item_egestao = {2137,92137,2138,92138,2139,92139}>} process_gestion),

Count({<num_item_egestao = {2142,92142}>} process_egestao),

-Count({<num_item_egestao = {2152,92152}>} process_egestao),

-Count({<num_item_egestao = {2192,92192}>} process_egestao),

-Count({<num_item_egestao = {2195,92195}>} process_egestao)

, organ, judge, cabinet))

Re: PivotTable - Sum Error

luiz_damascena — Fri, 02 Mar 2018 15:09:17 GMT

Perfect, Sunny!

Solved the question!

I will study the functions you used, for a better understanding, but the problem is gone!

Thank you very much!

Re: PivotTable - Sum Error

sunny_talwar — Fri, 02 Mar 2018 15:12:25 GMT

I think what you need to understand for the totals can be understood by looking here

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

The other change I did was... instead of checking if(A>B, A-B, 0)... I did this RangeMax(A-B, 0)... which just means that if A-B is positive, it will pick A-B, but if it is negative... it will pick 0 because 0 is bigger than the negative number.

Re: PivotTable - Sum Error

luiz_damascena — Fri, 02 Mar 2018 15:21:22 GMT

I will look at the link indicated. Thank you!

About using the RangeMax, good move ...

Thank you again!