6 Replies Latest reply: Jul 2, 2011 11:49 AM by Johan Vermeulen

# Totals problem in expression. Expression total versus sum of rows

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

• ###### Totals problem in expression. Expression total versus sum of rows

Hi,

Regards,

Kaushik Solanki

• ###### Totals problem in expression. Expression total versus sum of rows

Did you see the Excel enclosure?

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

Kind regards,

Johan.

• ###### Totals problem in expression. Expression total versus sum of rows

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.

• ###### Re: Totals problem in expression. Expression total versus sum of rows

Thanks for the hint.

I tried it, but without success.

Kind regards,

Johan.

• ###### Re: Totals problem in expression. Expression total versus sum of rows

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

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

• ###### Re: Totals problem in expression. Expression total versus sum of rows

Thanks John, you helped me out here.