Month selection in Set analysis

[chaitanyajami]

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

[adamdavi3s]

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

[chaitanyajami]

Hi Davies,

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

[ElizaF]

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.

[chaitanyajami]

Hi FILIP,

Thanks for suggestion , i would try this.