Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I'm about to set up visualizations of KPIs in Qlik and have the following problem / data (as an example):
Product_Group | Product Nr. | Amount | Latest_Update |
1 | 1 | 10 | 2024-06-07 |
1 | 2 | 15 | 2024-06-07 |
1 | 2 | 20 | 2024-06-03 |
1 | 3 | 30 | 2024-05-30 |
Currently, I'm using this formula:
SUM({$ < Product_Group = {$(=1)}, Latest_Update = {"$(=MAX(Latest_Update))"}>} Amount)
What I receive is 25, as MAX(Latest_Update) is 2024-06-07 and so I get the SUM of row 1 and 2 of the table above. I would expect to receive 55, so the SUM would calculate from Product 1, latest entry of product 2 and product 3.
With SQL I would solve it with an easy Group by... How do I do it in Qlik?
Check with this:
SUM(
AGGR(
IF(
Latest_Update = MAX({$<Product_Group = {1}>} Latest_Update),
Amount
),
Product_Group,
Product Nr.
)
or
SUM(
AGGR(
FirstSortedValue(Amount, -Latest_Update),
Product_Group,
Product Nr.
)
)
***Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.***
)
Hi,
thanks for the quick reply!
I tried the first option as it's the one with the filter by product group.
It appears to work well but I noticed that when there is more than 1 entry in "Latest Update" per Product_Group and Prouduct_Number, it stops working. When I select one specific Date in "Latest_Update", it works well, but thats what I wanted to avoid in the first place.
Is there anything else I could try?