topic Re: Less than specific MonthName value in Set Analysis in QlikView

Less than specific MonthName value in Set Analysis

Qlik_ULG — Sat, 16 Nov 2024 04:34:01 GMT

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)

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?

Re: Less than specific MonthName value in Set Analysis

tincholiver — Mon, 04 Feb 2019 16:40:49 GMT

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..

Re: Less than specific MonthName value in Set Analysis

sunny_talwar — Mon, 04 Feb 2019 17:18:32 GMT

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

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

Re: Less than specific MonthName value in Set Analysis

Qlik_ULG — Mon, 04 Feb 2019 18:10:16 GMT

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.

Re: Less than specific MonthName value in Set Analysis

sunny_talwar — Mon, 04 Feb 2019 18:16:41 GMT

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)

Re: Less than specific MonthName value in Set Analysis

Qlik_ULG — Tue, 05 Feb 2019 14:23:19 GMT

Thank you, Sunny.

This resolved the issue.

Much appreciated.