Totals problem in expression. Expression total versus sum of rows

[#1]

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.

[kaushiknsolanki]

Hi,

    Please upload the qvw file.

Regards,

Kaushik Solanki

[#3]

Did you see the Excel enclosure?

If you want I can upload it into QV, but probably not today.

Kind regards,

Johan.

[brenner_martina]

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.

[#5]

Thanks for the hint.

I tried it, but without success.

Please check the uploaded qvw file.

Kind regards,

Johan.

[johnw · Accepted solution]

I think this is what you're looking for in the "Retented sales" column:

sum(aggr(rangemin(sum(Sales09),sum(Sales10)),Customer,Product))

[#7]

Thanks John, you helped me out here.

Enjoy your weekend,

Johan.