Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
matias518
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:

codefcst_datesale_dateoffset_dateqty

Need YTD Sum Measure

(Desired Result should be this)

abc1231/1/20201/1/2020-3-0
abc1232/1/20202/1/2020-2-100
abc1233/1/20203/1/2020-1-190
abc1234/1/20204/1/20200-270
abc123Actual1/1/2020-1000
abc123Actual2/1/2020-900
abc123Actual3/1/2020-800

 

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
matias518
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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.