Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
This works as required I think -
Hi,
Can you send me sample qvw file?
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!!
Hi Tan,
Couldo be this possibility?
Regards
Miguel del Valle
Check this -
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
This works as required I think -
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))
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
It worked! Thank you Qlikview Community! I learnt something new along the way. Much appreciated