Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

luiz_damascena
New Contributor III

PivotTable - Sum Error

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.

LC

1 Solution

Accepted Solutions

Re: PivotTable - Sum Error

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)

          ),

    0)

, organ, judge, cabinet))

View solution in original post

6 Replies

Re: PivotTable - Sum Error

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)

          ),

     0)

, organ, judge, cabinet))


luiz_damascena
New Contributor III

Re: PivotTable - Sum Error

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

Re: PivotTable - Sum Error

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)

          ),

    0)

, organ, judge, cabinet))

View solution in original post

luiz_damascena
New Contributor III

Re: PivotTable - Sum Error

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!

LC

Re: PivotTable - Sum Error

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/NestedAggr...

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.

luiz_damascena
New Contributor III

Re: PivotTable - Sum Error

I will look at the link indicated. Thank you!

About using the RangeMax, good move ...

Thank you again!

LC