3 Replies Latest reply: Jan 18, 2016 4:56 AM by David Gutiérrez Melendro

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()))

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)'

Kind Regards

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

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

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)

• 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:

*,

AutoNumber(Month_Name,'MonthCounter') as MonthCounter

;

Date,

MonthName(Date) AS Month_Name,

Year(Date) as Year

Resident Temp

order by Date;

LET vCurrentMonth = Max(MonthCounter) ;

LET vPreviousMonth = Max(MonthCounter)-1;

• 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