Remove 0 value from calculation in subtotal field expressed in Pivot Table

[#1]

Hi I am having a challenge removing subtotal field from a Pivot table.

For example my data is expressed in the following format

Current Output

Date                    Location              Sales              Expenses              Taxable Amount        Tax Charged (0.25 * Taxable Amt)

                                  A                    20                        100                            0                                  0

1st May 2016              B                  100                        80                            20                                  5

                                  C                  120                      100                            20                                  5

                              Total                    240                      280                          -40                                  -69.75                

Desired Output

Date                    Location              Sales              Expenses              Taxable Amount        Tax Charged (0.25 * Taxable Amt)

                                  A                    20                        100                            0                                  0

1st May 2016              B                  100                        80                            20                                  5

                                  C                  120                      100                            20                                  5

                              Total                    240                      280                          40                                  10

Issues

As evident, the main issue is that my data for the column named Taxable amount is expressed as a calculated expression

if((Sum (Sales)-Sum ([Expenses])) < 0, 0, (Sum (Sales)-Sum ([Expenses])))

It achieved my desired output for the taxable amount and taxes. However the main issue arises when I expressed the subtotal in a Pivot Table. I do not wish to include values that are 0 in the calculation of the subtotal.

Can anyone advise me on how to achieve the desired output expressed in pivot table?                               

[muthukumar77]

Hi,

Can you send me sample qvw file?

[Anonymous]

Hi TanHong,

For taxable amount:

If(Dimensionality() < 2,

     Fabs(Sum(Sales)-Sum(Expenses)),

     Sum(Sales)-Sum(Expenses))

For tax amount:

If(Dimensionality() < 2,

     Fabs(Sum(Sales)-Sum(Expenses)),

     Sum(Sales)-Sum(Expenses))*0.25

Regards!!

[migueldelval]

Hi Tan,

Couldo be this possibility?

Regards

Miguel del Valle

[Digvijay_Singh]

Check this -

[#6]

thank you all for the help here. Unfortunately it did not solve my issue .

My guess is perhaps my questions was poorly formatted resulting in the confusion.

I am only interested in calculating the subtotal using the value calculated in my GP and Taxes's expressions. I have tried aggre() and dimensionality() but it appears those solutions are not exactly what I am looking for.

The main reasons is because Qlikview take into considerations of my calculated negative gross profit value instead of using 0 as stated in my GP expressions

[Digvijay_Singh · Accepted solution]

This works as required I think -

[Digvijay_Singh]

Gross Profit exp -

Sum(Aggr(if(Sum(Sales)-Sum(Expenses)<0,0,Sum(Sales)-Sum(Expenses)),Date,Location,Store))

Taxes -

Sum(Aggr(if(0.10*(Sum(Sales)-Sum(Expenses))<0,0,0.10*(Sum(Sales)-Sum(Expenses))),Date,Location,Store))

[jonathandienst]

You can simplify the expressions like this:

Taxable:

Sum(Aggr(RangeMax(0, Sum(Sales) - Sum(Expenses)), Date, Location, Store))

Tax:

Sum(Aggr(RangeMax(0, Sum(Sales) - Sum(Expenses)), Date, Location, Store)) * 0.25

[#10]

It worked! Thank you Qlikview Community! I learnt something new along the way. Much appreciated