Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

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

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:


Need YTD Sum Measure

(Desired Result should be this)



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.


2 Replies
Contributor II
Contributor II

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.

Contributor II
Contributor II

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.