QS- Sum where date less than

[Anonymous]

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

[sunny_talwar]

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)

[Anonymous]

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

[sunny_talwar]

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

[zebhashmi]

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)