4 Replies Latest reply: Mar 27, 2017 9:57 PM by Sunny Talwar

Aggregate in Script based on limited dimensions

Hi,

I would like to script a Calculated revenue based of a threshold value of 42.

Ik have the following data (column 1 to 4 source data, column 5 to 7 expected result after scripting.

The result I am looking for is:

ProductUnitDateRevenueAggr Revenue

Aggr Revenue

with threshold

Revenue

with threshold

A12001.01.201710101010
A14002.01.201720303020
A12003.01.201715454212
A14004.01.201755000

The result I get at this moment is:

ProductUnitDateRevenueAggr Revenue

Aggr Revenue

with threshold

Revenue

with threshold

A12001.01.201710101010
A14002.01.201720202020
A12003.01.201715353515
A14004.01.2017525255

So, the sum/aggr condition on the Revenue column works, except I didn't manage to make clear in my script that this aggregation should be based on only the dimensions product and Date.

Who can help me out?

Thanks in advance,

Nicole

• Re: Aggregate in Script based on limited dimensions

Load *,

If (Product=peek(Product),Rangesum(Peek([AGGR_Revenue]),Revenue),Revenue) as AGGR_Revenue,

If (Product=peek(Product),Rangesum(Peek([AGGR_Revenue_threshold]),Revenue_threshold),Revenue_threshold) as AGGR_Revenue_threshold

Resident

Table;

Order by Product,Date ASC;

Also if there are aggregations based on dates for revenues then use Group by clause in script;

• Re: Aggregate in Script based on limited dimensions

May be this:

Table:

LOAD * INLINE [

Product, Unit, Date, Revenue

A, 120, 01.01.2017, 10

A, 140, 02.01.2017, 20

A, 120, 03.01.2017, 15

A, 140, 04.01.2017, 5

];

FinalTable:

LOAD *,

If(Product = Previous(Product) and [Aggr Revenue with threahbold] > 0, [Aggr Revenue with threahbold] - Previous([Aggr Revenue with threahbold]), [Aggr Revenue with threahbold]) as [Revenue with threshold];

LOAD *,

If(Product = Previous(Product) and Previous([Aggr Revenue with threahbold Temp]) = 42, 0, [Aggr Revenue with threahbold Temp]) as [Aggr Revenue with threahbold];

LOAD *,

If(Product = Previous(Product), RangeSum(Peek('Aggr Revenue') + Revenue), Revenue) as [Aggr Revenue],

If(Product = Previous(Product), RangeMin(RangeSum(Peek('Aggr Revenue') + Revenue), 42), Revenue) as [Aggr Revenue with threahbold Temp]

Resident Table

Order By Product, Date;

DROP Table Table;

DROP Field [Aggr Revenue with threahbold Temp];