topic Month selection in Set analysis in QlikView

Month selection in Set analysis

chaitanyajami — Fri, 24 Mar 2017 10:21:42 GMT

Hi All,

Iam calculating sum of revenues by below expression where im facing a challenge to include the month selection, my requirement is

if user select a year last three months data should be shown in different columns like for 2016 last three months would be Dec,nov,october 16 . for 2017 it should be like dec16, jan 17, feb 17 .im writing my expression as below but when i select year as 2017 , dec 2016 data is showing as "0", is there any way i can achieve it.

vYearSel = Max(Year)

=Num(Round(Sum({<[Regional Leader] =,[Division Flag]=,MonthName=,Year={$(=vYearSel)},MonthName={"$(=Month(Addmonths(Today(),-1)))"}>} Revenue)),'$#,000')

=Num(Round(Sum({<[Regional Leader] =,[Division Flag]=,MonthName=,Year={$(=vYearSel)},MonthName={"$(=Month(Addmonths(Today(),-2)))"}>} Revenue)),'$#,000')

=Num(Round(Sum({<[Regional Leader] =,[Division Flag]=,MonthName=,Year={$(=vYearSel)},MonthName={"$(=Month(Addmonths(Today(),-3)))"}>} Revenue)),'$#,000')

Re: Month selection in Set analysis

adamdavi3s — Fri, 24 Mar 2017 11:30:18 GMT

This is because your set is trying to look at december 2017

Maybe try something like (and I have written this on the fly so excuse any obvious mistakes)

vYearSel1 = if(num(Max(Month))=1,Max(Year)-1,Max(Year))

vYearSel2 = if(num(Max(Month))<=2,Max(Year)-1,Max(Year))

vYearSel3 = if(num(Max(Month))<=3,Max(Year)-1,Max(Year))

=Num(Round(Sum({<[Regional Leader] =,[Division Flag]=,MonthName=,Year={$(=vYearSel1)},MonthName={"$(=Month(Addmonths(Today(),-1)))"}>} Revenue)),'$#,000')

=Num(Round(Sum({<[Regional Leader] =,[Division Flag]=,MonthName=,Year={$(=vYearSel2)},MonthName={"$(=Month(Addmonths(Today(),-2)))"}>} Revenue)),'$#,000')

=Num(Round(Sum({<[Regional Leader] =,[Division Flag]=,MonthName=,Year={$(=vYearSel3)},MonthName={"$(=Month(Addmonths(Today(),-3)))"}>} Revenue)),'$#,000')

Re: Month selection in Set analysis

chaitanyajami — Sun, 26 Mar 2017 07:21:53 GMT

Hi Davies,

Thanks for suggestion, i would try and check its working .

Re: Month selection in Set analysis

ElizaF — Sun, 26 Mar 2017 10:30:26 GMT

Hello,

Try this solution.

In Calendar Table use AutoNumber function, like this:

Calendar:

Load

AutoNumber(YearMonth, 'YearMonth') as _M

;

LOAD

Date(Temp_Date, 'D/M/YYYY') as Date,

Year(Temp_Date) as Year,

Date(MonthStart(Temp_Date), 'MMM-YYYY') as YearMonth,

Month(Temp_Date) as MonthName

;

LOAD

$(vMinDate) + IterNo() - 1 as Temp_Date

AUTOGENERATE (1) WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

and change the expressions, as below:

=Num(Round(Sum({<_M={$(=max(_M)-1)},[Regional Leader] =,[Division Flag]=,Year=,MonthName=>} Revenue)),'$#,000')

=Num(Round(Sum({<_M={$(=max(_M)-2)},[Regional Leader] =,[Division Flag]=,Year=,MonthName=>} Revenue)),'$#,000')

=Num(Round(Sum({<_M={$(=max(_M)-3)},[Regional Leader] =,[Division Flag]=,Year=,MonthName=>} Revenue)),'$#,000')

Hope this helps.

Re: Month selection in Set analysis

chaitanyajami — Wed, 29 Mar 2017 04:48:28 GMT

Hi FILIP,

Thanks for suggestion , i would try this.