Set analysis smaller than current Month

[juriengroot]

  Hi,

I am trying to get Sales smaller than current month.

Below works fine for current month.

Sum({$<Month={'$(=Month(Today()))'}>}[Sales])

When adding:

Sum({$<Month={<='$(=Month(Today()))'}>}[Sales])

It doesn't work. I tried so many variations..

Some help would be very much appreciated!

[sunny_talwar]

Try this:

Sum({$<Month={'<=$(=Month(Today()))'}>}[Sales])

[swuehl · Accepted solution]

What is the format of your month field?

Try creating a numeric month field

LOAD

     Month(Date) as Month,

     Num(Month(Date)) as MonthNum,

     ...

Then

Sum({$<MonthNum ={"<=$(=Num(Month(Today())))"}>} [Sales])

[sunny_talwar]

Or you can also use DateField:

Sum({$<Month={"$(='<' & Date(MonthStart(Today()), 'DateFieldFormatHere'))"}>} [Sales])

[juriengroot]

Thanks, does Qlik not understand the expression without nummeric value? Kind of strange, no?

[swuehl]

Yeah, could be kind of confusing / strange, see also the comments below

Dates in Set Analysis