Total not matching in Pivot Table

[su_pyae]

I have a pivot table similar to this and Min(Amount) column is not in the actual table. It is just there so that it is easier for me to explain. 

I used if statement to get the value of the Net Wholesales column. And then I am multiplying that column with Min(Amount) to get the Net Amount. 

However, the problem is that the TOTAL I get after enabling Totals for Code dimension aren't matching with the actual total. As can be seen from this example table, the total is using the formula to get the total value instead of summing it up. 

Is this how the total function is supposed to work? Or just my table is off? 

Thank you for your help.

[rubenmarin]

Hi, you will need a dedicated expressions for the totals, this can be done using Dimensionality() for row totals or SecondaryDimensionality() for column totals, when any of this functions returns 0, is the totals row/column.

And to calcualte the toal as a sum of rows you can do a Sum(Aggr([Expression], Dimension1, Dimension2...))

So if your expression is Min(Amount)*Sum(Sales) the expression could be:

If(Dimensionality()=0
  ,Sum(Aggr(Min(Amount)*Sum(Sales), Brand, Reg))
  ,Min(Amount)*Sum(Sales)
)

[su_pyae]

Hi, 

Thank you for the reply. This is my first time seeing/using Dimensionality. 

I used that expression but I am still getting the same total amount, not the total I want. 

[rubenmarin]

Hi, can post a sample app with some dummy data? and the values you expect to be returned

[su_pyae]

I am having trouble exporting the sample app. Maybe I am not allowed to do this? I am not sure. 
Here  is my sample script. 

Sample:

Load * Inline [
Brand, Reg, Code, Type, Number, Amount
AB, E, 1, CR, 1234, -10
AB, E, 1, CR, 1234, -10
AB, E, 1, CR, 1234, -10
AB, E, 2, DR, 3332, -29
AB, S, 1, DR, 1111, -50
AB, C, 3, DR, 1232, -100
AB, S, 1, DR, 1111, -50
AB, E, 2, DR, 3332, -29
AB, E, 2, DR, 3332, -29
AB, S, 1, DR, 1111, -50
AB, C, 3, DR, 1232, -100
AB, S, 1, DR, 1111, -50
AB, E, 2, DR, 3332, -29
AB, S, 1, DR, 1111, -50
AB, C, 3, DR, 1232, -100
AB, S, 1, DR, 1111, -50
AB, C, 3, DR, 1232, -100
BC, S, 4, CR, 1234, -200
BC, S, 3, CR, 2221, -43
BC, S, 4, CR, 1234, -200
BC, S, 3, CR, 2221, -43
BC, S, 4, CR, 1234, -200
BC, S, 3, CR, 2221, -43
BC, E, 5, DR, 3213, -30
BC, S, 4, CR, 1234, -200
BC, S, 3, CR, 2221, -43
BC, E, 5, DR, 3213, -30
BC, E, 5, DR, 3213, -30
BC, E, 5, DR, 3213, -30
BC, W, 5, DR, 1111, -20
BC, E, 5, DR, 3213, -30
BC, E, 5, DR, 3213, -30
BC, E, 5, DR, 3213, -30
BC, W, 5, DR, 1111, -20
BC, W, 1, CR, 7653, -50
CD, E, 2, CR, 5672, -10
CD, S, 1, CR, 4676, -33
CD, W, 2, DR, 1111, -65
CD, W, 2, DR, 1111, -65
CD, W, 2, DR, 1111, -65
CD, W, 2, DR, 1111, -65
CD, C, 9, CR, 1234, -75
CD, W, 8, DR, 1111, -60
CD, E, 2, CR, 5672, -10
CD, S, 1, CR, 4676, -33
CD, E, 2, CR, 5672, -10
CD, S, 1, CR, 4676, -33
CD, E, 2, CR, 5672, -10
CD, S, 1, CR, 4676, -33
CD, E, 2, CR, 5672, -10
CD, S, 1, CR, 4676, -33
CD, W, 2, DR, 1111, -65
CD, W, 8, DR, 1111, -60
CD, W, 8, DR, 1111, -60
CD, W, 8, DR, 1111, -60
];

App:
LOAD *,
If (Type = 'CR', Number) as Credit,
If (Type = 'DR', Number) as Debit
Resident Sample;
Drop Table Sample;

I am using these formula to calculate NS and NA. 

NS	If (Count( Credit) > 0, Count( Debit)-Count( Credit), Count( Debit))
NA	If ($(varNS) < 0, Min(Amount)*$(varNS), -(Min(Amount)*$(varNS)))

varNS is the same formula as NS. 

As can be seen from the attached photo, the total for NA column is wrong whenever there is more than one line. 

If you look at 'CD' and 'W' section, total should be 565 instead of 585. Instead of summing up the value, it is using the sum of NS column and multiplied it with Min(Amount) to get the total of NA column. 

[rubenmarin · Accepted solution]

Hi, just adding the aggr it works for me:

Sum(Aggr(If ($(varNS) < 0,
Min(Amount)*$(varNS),
-(Min(Amount)*$(varNS))),Brand,Reg,Code))

Confirm that varNS doesn't has equal sign at start of the variable, that will tell Qlik to calculate it globally, not row by row, varNS should have:

If (Count( Credit) > 0,
Count( Debit)-Count( Credit),
Count( Debit))

[su_pyae]

Thank you! It works now 🙂