Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

QS- Sum where date less than

hey guys,

basically, I need to sum my sales from 2018 but only January and February. I want to exclude the current month.

I'm trying to do the following:

=sum({$<Date_Year={'$(=year(vMaxDateSalles))'}, Date_Month={"<$(=month(vMaxDateSalles))"}>} s_Salles)

vMaxDateSalles is a variable I created with the max Date.

Am I doing something wrong? I tried with ' instead of ", and also tried <= instead of < , just for curiosity, but 0 success.

Can you help me?

Thanks in advance,
Miguel Cunha

4 Replies
Highlighted

Why are you using Max Date if the goal is to exclude current month... why not use Today()? Also, suggest to create a MonthNum field like this

LOAD Date,

     Year(Date) as Date_Year,

     Month(Date) as Date_Month,

     Num(Month(Date)) as Date_MonthNum

and then try this

=Sum({$<Date_Year = {"$(=Year(Today()))"}, Date_MonthNum = {"<$(=Num(Month(Today()))"}>} s_Salles)

Highlighted
Creator
Creator

Yeah, it worked now. But I dont think I understood why. Why didn't it work with the Variable?

Highlighted

What was the Max(Date) in your application? If it was a date in March... there could have been other reasons why it did not work...

1) Format of the Month field... this is why I asked you to create a numbered month field

2) May be the variable needs dollar sign expansion

Highlighted
Specialist
Specialist

You can try two things with your expression

=sum({$<Date_Year={"=year($(vMaxDateSalles))"}, Date_Month={"=month($(vMaxDateSalles))"}>} s_Salles)

or

=sum({$<Date_Year={"$(=year(vMaxDateSalles))"}, Date_Month={"$(=month(vMaxDateSalles))"}>} s_Salles)