Aggregate in Script based on limited dimensions

[#1]

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:

Product	Unit	Date	Revenue	Aggr Revenue	Aggr Revenue with threshold	Revenue with threshold
A	120	01.01.2017	10	10	10	10
A	140	02.01.2017	20	30	30	20
A	120	03.01.2017	15	45	42	12
A	140	04.01.2017	5	50	0	0

The result I get at this moment is:

Product	Unit	Date	Revenue	Aggr Revenue	Aggr Revenue with threshold	Revenue with threshold
A	120	01.01.2017	10	10	10	10
A	140	02.01.2017	20	20	20	20
A	120	03.01.2017	15	35	35	15
A	140	04.01.2017	5	25	25	5

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

[aarkay29]

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;

[sunny_talwar]

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

[#4]

Thanks! This works.

[sunny_talwar]

Great, please close the thread by marking the correct response.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny