Using dynamic month in set analysis based on month-year selection

[#1]

Hi,

I've an analysis based on the actual month that compares values from this month to the previous one.

count( {$<Month={$(=Month(now()))},Year=>} Activation_id )

/count({$<Month={$(=Month(AddMonths(now(),-1)))},Year={$(=Year(AddMonths(now(),-1)))}>}Activation_id)-1

It works ok but, what I'd like to do now, is to change it so that if I select a month-year from a left side calendar, the analysis will consider the selected month against the month before.

I've a Calendar loaded in this way:

Calendar:

LOAD

DateId,

date(DateId) as DateEvent,

day(DateId) as Day,

week(DateId) as Week,

month(DateId) as Month,

dual(month(DateId) & '-' & year(DateId) , year(DateId) & num(month(DateId), '00')) as MonthYear,

year(DateId) as Year,

weekday(DateId) as Weekday,

(year(DateId)-1)*12 + Num(Month(DateId)) as MonthId,

'Q' & ceil(month(DateId)/3) as Quarter

RESIDENT Date_src;

What i tried to do is to change  {$<Month={$(=Month(AddMonths(now(),-1)))},Year={$(=Year(AddMonths(now(),-1)))}>} using MonthId but it did not work when I selected a specific month.

do you have any idea on how to do it?

[Gysbert_Wassenaar · Accepted solution]

If you use Qlikview Components to create a calendar from your DateId field you'll get a lot of nice calendar fields and set analysis modifiers with very little effort. If you don't want that you can try:

count( {$<MonthYear=,Month={$(=Month(max(DateId)))},Year=>} Activation_id )

/count({$<MonthYear=,Month={$(=Month(AddMonths(max(DateId),-1)))},Year={$(=Year(AddMonths(max(DateId),-1)))}>}Activation_id)-1

[#3]

thanks for the link: I will try to implement the calenda you suggested but it might take me a while.

In the meantime I tried your suggestion but it does not work: I split the formula and

count( {$<MonthYear=,Month={$(=Month(max(DateId)))},Year=>} Activation_id ) --> works ok returning values

count({$<MonthYear=,Month={$(=Month(AddMonths(max(DateId),-1)))},Year= {$(=Year(AddMonths(max(DateId),-1)))}>}Activation_id) --> does not return any value

[#4]

Hi Gysbert,

I realized the error occurred when i selected only the month without the year: I will work on it in order to take, by default, the current year if a user select only a month and not a month&year

thanks,

g