topic Aggregation if statment in QlikView

Aggregation if statment

zagzebski — Sat, 20 Jul 2013 19:59:43 GMT

I am struggling to write the proper expression to evalute at a product level how much of my last years income was retained (defined by having at least $1 in Current Year) and how much was lost. The problem is I need to summarize at a Rollup Product Level so I need aggregation (I think).

Here is my sample data:

Rollup Product Product :Last Year Rev Current Year Revenue

1 a 100 200

1 b 200 0

2 b 300 500

2 c 400 800

3 a 500 0

3 b 600 1100

What I want my object to look like:

Rollup Product Last Year Rev (Lost) Last Year Revenue (Retained)

1 200 100

2 0 700

3 500 600

Re: Aggregation if statment

Gysbert_Wassenaar — Sun, 21 Jul 2013 08:48:00 GMT

You can calculate this in the script (recommended):

T1:

LOAD *

,if([Last Year Rev]>[Current Year Revenue], [Last Year Rev]-[Current Year Revenue] ,0) as Lost

,if([Last Year Rev]<=[Current Year Revenue], [Last Year Rev] ,0) as Retained

INLINE [

Rollup Product, Product, Last Year Rev, Current Year Revenue

1, a, 100, 200

1, b, 200, 0

2, b, 300, 500

2, c, 400, 800

3, a, 500, 0

3, b, 600, 1100

];

Or with expressions in a chart with Rollup Product as dimension:

Lost: sum(if([Last Year Rev]>[Current Year Revenue], [Last Year Rev]-[Current Year Revenue]))

Retained: sum(if([Last Year Rev]<=[Current Year Revenue], [Last Year Rev]))

Re: Aggregation if statment

zagzebski — Sun, 21 Jul 2013 13:20:03 GMT

The problem with the expression is the data has to be evaluated at the Product dimension first. If I do it at the Rollup Product dimension then all Revenue would go to the "Retained" column because at a Product Rollup level there is a least $1 in total across multiple products. It would result in the following.:

Rollup Product Last Year Rev (Lost) Last Year Revenue (Retained)

1 0 300

2 0 700

3 0 1100