Double Counting Dimension Table

[QlikBusinessUser]

Hi. I have a standard dimension table with price changes for a subscription changing over time. Example of simplified table:

SubscriptionID, ContractID, StartDate, EndDate, Price

1, 10, 1 Jan 2024, 5 Jan 2024, 10

2, 10, 6 Jan 2024, 19 Feb 2024, 12

3, 10, 20 Feb 2024, 31 Dec 2024, 18

I need to sum all subscriptions between two dates from a master calendar in a measure as below. I cannot use a ranking to select the max subscription as it needs to align with the dates. If two rows lie within the period I am getting from the master calendar, I want to take the maximum price.

E.g. Sum for master calendar 1 Jan - 31 Jan: 12

E.g. Sum for master calendar 1 Jan - 31 Dec: 18

Measure below:

Sum( { < [StartDate.autoCalendar.Date] = {"$(='>=' & Min([DateDay.autoCalendar.Date]) & '<=' & Max([DateDay.autoCalendar.Date]) )"} > } Price)

DateDay is from the master calendar.

Thank you!

[Chanty4u · Accepted solution]

Try this

Sum({<StartDate = {"<=$(=Max([DateDay.autoCalendar.Date]))"}, EndDate = {">=$(=Min([DateDay.autoCalendar.Date]))"}>} Aggr(Max(Price), SubscriptionID))

[QlikBusinessUser]

Thank you, that works perfectly for what I'm after. Out of interest, how would I change the script for cases if the price could actually go down? Go instead of using the Max Price per subscription ID, I wanted to use the price that corresponds with the max rank per subscription ID (all within the period selected from the master calendar)? Thank you