- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
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
- Tags:
- qlikview_scripting
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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];
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks! This works.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Great, please close the thread by marking the correct response.
Qlik Community Tip: Marking Replies as Correct or Helpful
Best,
Sunny