Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

4 Replies
aarkay29
Valued Contributor

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

Capture.PNG

Not applicable

Re: Aggregate in Script based on limited dimensions

Thanks! This works.

Re: Aggregate in Script based on limited dimensions

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

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny