Multiple Conditions Sum - Max Ranking per ID

[QlikBusinessUser]

Hi,

I have a table where I need to sum the my notification date is between the max and min of my date from the master calendar. This is easily done using the formula below.

Sum( { < [NotificationDate.autoCalendar.Date] = {"<=$(= Max([DateDay.autoCalendar.Date]) )"} , [NotificationDate.autoCalendar.Date] = {">=$(= Min([DateDay.autoCalendar.Date]) )"} > } TotalPriceUSDFinal)*12

My issue is that my table can have multiple rows per ID based on a ranking column which will mean some are double counted just using the formula above.

The dates will remain the same but the price will change. I need the price for the max ranking included in my multiple condition sum if above.

Example of table:

ID, Ranking, TotalPriceUSDFinal, Notification Date,

1, 1, 10, 01-02-2024

1, 2, 12, 01-02-2024

1, 3, 13, 01-02-2024

2, 1, 20, 10-02-2024

2, 2, 22, 10-02-2024

Thank you

[Antoine04 · Accepted solution]

Hello,

My advice would be to create a specific field in the data load script, something like this :

And then in the analyse :

Does it fit your expectations ?

Best regards,

Antoine L

[Brian_C]

Hi How about using a resident load and sorting the data by ID and notification date then creating a flag using the previous function so that if the id equals the previous id and the notification date equals the previous then set the flag to 1 else zero then use the flag in your expression.

Kindest regards

Brian