Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm calculation values in a expression column. In another column the result is alted by an IF function.
On total level this IF function does not work correctly. It should use the total headers of the expressions, not the line values.
I tried the option 'sum of rows' and expression totals, the results are different but not correct.
HOW IT SHOULD BE:
Customer | Product | Maintenance 2009 | Maintenance 2010 | Retented Sales | Retention rate |
A | X | 100 | 0 | 0 | 0% |
A | Y | 200 | 270 | 200 | 100% |
A | Z | 100 | 80 | 80 | 80% |
TOTAL A | . | 400 | 350 | 280 | 70% |
B | X | 20 | 100 | 20 | 100% |
B | Y | 200 | 120 | 120 | 60% |
TOTAL B | . | 220 | 220 | 140 | 64% |
How Qlikview does it
Customer | Product | Maintenance 2009 | Maintenance 2010 | Retented Sales | Retention rate | |
A | X | 100 | 0 | 0 | 0% | |
A | Y | 200 | 270 | 200 | 100% | |
A | Z | 100 | 80 | 80 | 80% | |
TOTAL A | . | 400 | 350 | 280 | 180% | USING SUM OF ROWS |
B | X | 20 | 100 | 20 | 100% | |
B | Y | 200 | 120 | 120 | 60% | |
TOTAL B | . | 220 | 220 | 140 | 100% | USING EXPRESSION TOTAL |
Retented sales: Sales which have occurred in 2010 as a follow up on 2009. No new sales
Please check enclosed excel file for the formulas of the example
Hope you can help me.
Kind regards,
Johan Vermeulen
K3 Business Solutions.
I think this is what you're looking for in the "Retented sales" column:
sum(aggr(rangemin(sum(Sales09),sum(Sales10)),Customer,Product))
Hi,
Please upload the qvw file.
Regards,
Kaushik Solanki
Did you see the Excel enclosure?
If you want I can upload it into QV, but probably not today.
Kind regards,
Johan.
Hi,
you have to use the function dimensionality() resp. secondarydimensionality(). Enter dimensionality() as a new dimension, than you can see, which value the partial sum has (0,1,2,...) And then you can enter an if-Statement like:
If(Dimensionality()=1, '1 partial sum',...)
SecondaryDimensionality() is for columns.
Greetings from Munich.
Thanks for the hint.
I tried it, but without success.
Please check the uploaded qvw file.
Kind regards,
Johan.
I think this is what you're looking for in the "Retented sales" column:
sum(aggr(rangemin(sum(Sales09),sum(Sales10)),Customer,Product))
Thanks John, you helped me out here.
Enjoy your weekend,
Johan.