topic Re: Aggregate in Script based on limited dimensions in QlikView

Aggregate in Script based on limited dimensions

Mon, 27 Mar 2017 13:43:56 GMT

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

Re: Aggregate in Script based on limited dimensions

aarkay29 — Mon, 27 Mar 2017 14:50:30 GMT

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

sunny_talwar — Mon, 27 Mar 2017 15:02:35 GMT

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

Re: Aggregate in Script based on limited dimensions

Mon, 27 Mar 2017 21:19:18 GMT

Thanks! This works.

Re: Aggregate in Script based on limited dimensions

sunny_talwar — Tue, 28 Mar 2017 01:57:30 GMT

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

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny