Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

How to include and if sentence on a set analytic?

Hello guys,

After several days with the same problem, I need some help.

Right now I got a couple of variables like this:

vCurrentMonth = =num(Month(Today()))

vCurrentYear = =num(Year(Today()))

vPreviousMonth  = =num(Month(AddMonths(Date(Today()),-1)))

vPreviousYear = =num(Year(AddYears(Date(Today()),-1)))

Witch I use along the qlv. This way I can show on a table data from the actual Month and Year as follows ...

=sum({1<month={'$(vCurrentMonth)'},year={'$(vCurrentYear)'} >} events)

and for the previous month:

=sum({1<month={'$(vPreviousMonth)'} ,year={'$(vCurrentYear)'}>} events

This works for every case except for January, because obviously the year changes. How can I include and if sentence on the code for the previous month, with the next logic?

If (month = '1') ]

       year={'$(vPreviousYear)'

else

        year={'$(vCurrentYear)'

Thanks in advance,

Kind Regards

3 Replies
MVP
MVP

Re: How to include and if sentence on a set analytic?

I would rather create a consecutive Month counter in the script, like

LOAD

     month,

     year,

     12*year + month as monthcounter,

     ....

Then use this monthcounter in the set analysis to address the previous month

vPrevMonthCounter = 12*Year(Today()) + Month(Today()) -1;

=sum({1< monthcounter = {'$(vPrevMonthCounter)'} >} events)

Not applicable

Re: How to include and if sentence on a set analytic?

I would recommend MonthCounter in your Master Calendar table that holds values like 1,2,3,4 for each month ascending order.

MasterCalendar:

Load

     *,

     AutoNumber(Month_Name,'MonthCounter') as MonthCounter

;

Load

     Date,

     MonthName(Date) AS Month_Name,

     Year(Date) as Year

Resident Temp

order by Date;

LET vCurrentMonth = Max(MonthCounter) ;

LET vPreviousMonth = Max(MonthCounter)-1;

Not applicable

Re: How to include and if sentence on a set analytic?

Thank you very much for both answers!

I finally managed to find an alternative solution, cause i do not have a master calendar as u both present.

In case someone finds this useful,

vCurrentYear = = if(getfieldselections(month)='1',num(Year(AddYears(Date(Today()),-1))) , num(Year(Today())))

This worked!

But anyway ty again!

Kind regards

Community Browser