Less than specific MonthName value in Set Analysis

[Qlik_ULG]

Hi all,

I am currently having an issue calculating a total using set analysis where the MonthName value is defined as less than a specific MonthName.

Calculating the total where MonthName is less than today's MonthName is fine:

sum({<[ProjectMonthName]={"<$(=Num(MonthName(today())))"}>} ProjectTotal)

Likewise any calculation where we require it equal to a specific MonthName is also fine:

sum({<[ProjectMonthName]={"Jan 2019"}>} ProjectTotal)

However, I have been unable to return the correct values when trying to set it as less than a defined MonthName:

E.g. sum({<[ProjectMonthName]={"<$(=Num('Jan 2019')"}>} ProjectTotal)

Or

sum({<[ProjectMonthName]<={"Jan 2019"}>} ProjectTotal)

I'm aware that neither of the above syntax are correct for a number of reasons, but I haven't been able to find a solution.

Is this possible in set analysis?

[tincholiver]

Hi bro,
I do not know if there is a better solution but after investigating a bit I found the following solution and it worked very well.
in the master calendar add the following line:
(Year (PeriodDate) * 12) + num (month (PeriodDate)) AS MES_ANT,
then the result is a correlative number which can be compared with a previous one.
To be able to compare you must generate the variables that will be taken each month:
LET vMes00 = '= Max (MES_ANT)';
LET vMes01 = '= Max (MES_ANT) -1';

and in the expression of the graph:

sum ({<MES_ANT = {'$ (vMes00)'}>} Value), this will be the selected month or the month of the record with the highest date,
and sum ({<MES_ANT = {'$ (vMes01)'}>} Value), this will be in the previous month and so on for how many months you want to compare. (You must generate the variable with the number of months to back that you want to analyze)

Results its something like this:

Let me know if you need more help.

Wicham..

[sunny_talwar]

How exactly do you create ProjectMonthName in the script? May be try this

Sum({<[ProjectMonthName] = {"<$(=Num(Date#('Jan 2019', 'MMM YYYY')))"}>} ProjectTotal)

[Qlik_ULG]

Thanks for the response, Sunny.

I have ProjectMonthName created using the MonthName function on the ProjectDate in the script.: MonthName(ProjectDate) As ProjectMonthName

My date format is set as 'DD/MM/YYYY'. I don't have a format set for MonthName prior to the table load. Should I define this?

However, it does work correctly for any expressions using MonthName(Today()).

Your suggestion returns zero values. What's interesting though, is that if I use your suggestion with 'greater than' it returns the overall total, so it does seem to be reading it in some manner.

[sunny_talwar · Accepted solution]

Instead of creating the field using MonthName, try this

Date(MonthStart(ProjectDate), 'MMM-YYYY') As ProjectMonthName

and then try this

Sum({<[ProjectMonthName] = {"<$(=Date(Date#('Jan-2019', 'MMM-YYYY'), 'MMM-YYYY'))"}>} ProjectTotal)

[Qlik_ULG]

Thank you, Sunny.

This resolved the issue.

Much appreciated.