Aggregation if statment

[zagzebski]

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

[Gysbert_Wassenaar]

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]))

[zagzebski]

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