Show sum of latest entry by product

[its_steveha]

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?

[TauseefKhan]

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.***
)

[its_steveha]

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?