How to do a SUMIF function, based on two dimension dates

[matias518]

Hi this is my first post. I've got an issue, but can't seem to figure it out. There is something I can do in Excel, and trying to figure out how to do it in Qlik Sense.

I have a table like below:

code	fcst_date	sale_date	offset_date	qty	Need YTD Sum Measure (Desired Result should be this)
abc123	1/1/2020	1/1/2020	-3	-	0
abc123	2/1/2020	2/1/2020	-2	-	100
abc123	3/1/2020	3/1/2020	-1	-	190
abc123	4/1/2020	4/1/2020	0	-	270
abc123	Actual	1/1/2020	-	100	0
abc123	Actual	2/1/2020	-	90	0
abc123	Actual	3/1/2020	-	80	0

All the historical qty data does not have a fcst_date, (so table states "Actual") . I would like a running YTD total for qty but posted for the fcst_date rows only.

I'm trying to do a sum if like so and can get this to work in excel:

sum qty by sale_date if less than fcst_date.

In QLik sense I tried:

sum({$<sale_date= {"< fcst_date"} >}qty)

but I get all zeroes. I looked around in the forums, and I've seen Rangesum(above) , but I dont think I can use that as the real table is much more complicated than the above, and users can select individual sale_date / fcst_date which, I think would mess up the rangesum. 

Anyway, I could use some help. Thank You.

[matias518]

I've added a new field called offset_date, which I'm using in a MonthsStart expression (from 0 to -3 months), I also added an Aggr function. and I'm trying this below, but I get all zeroes:

Sum( total {$<sale_date={ "<$(= monthsStart(1,max(TOTAL{1}sale_date), offset_date) )"} >} aggr(sum(qty),code,sale_date))

If I replace offset_date above, with say -1, and I do get a value of 190 for all rows. But thats not what I need. I want each row to be calculated independently, based on the logic sum(If(start_date < fcst_date) as displayed in my main table above. But can't seem to figure out the proper way to write this expression.

[matias518]

Well, I created a nested If statement that gives me the result I was looking for. But I've got to think that there is a better way.

if( offset_date = 0,
Sum( total {$<sale_date={ "<$(= monthsStart(1,max(TOTAL{1}sale_date), 0) )"} >} qty),
if( offset_date = -1,
Sum( total {$<sale_date={ "<$(= monthsStart(1,max(TOTAL{1}sale_date), -1) )"} >} qty),
if( offset_date = -2,
Sum( total {$<sale_date={ "<$(= monthsStart(1,max(TOTAL{1}sale_date), -2) )"} >} qty),
if( offset_date = -3,
Sum( total {$<sale_date={ "<$(= monthsStart(1,max(TOTAL{1}sale_date), -3) )"} >} qty)
))))

PS> reason I'm using monthsStart is that in the actual report, max(sale_date) could land with any date in the month.