topic Re: Prior Year same month Dynamic in QlikView

Prior Year same month Dynamic

jeckstein — Wed, 25 Nov 2020 16:16:04 GMT

I am looking to use set analysis to change this code which sums value depending on the max(MonthYearID).

This is dynamic so I can filter on past MonthYearID and the max would change to that date.

Num(
count(
{<MonthYearID={$(=max({<FutureFlag={0}>}MonthYearID))},FutureFlag={0}>}
Value)
,'#,##0')

I am looking to take this code and use the same functionality but instead of showing me value for max(MonthYearID). I need max(MonthYearID)-12. This will show me the previous year same month dynamically.

Meaning if i select September 2016 I will get Value from September 2015. I think is should be something like this but nothing seems to be working.

Num(
count(
{<MonthYearID={$(=max({<FutureFlag={0}>}MonthYearID)-24)},FutureFlag={0}>}
Value)
,'#,##0')

Thanks in advanced.

Re: Prior Year same month Dynamic

sunny_talwar — Wed, 02 Nov 2016 18:33:27 GMT

May be like this:

Num(Count({<MonthYearID={$(=Num(AddMonths(Max({<FutureFlag={0}>}MonthYearID), -12)))},FutureFlag={0}>} Value),'#,##0')

Re: Prior Year same month Dynamic

jeckstein — Wed, 02 Nov 2016 18:38:53 GMT

Sunny,

That does not seem to work

Re: Prior Year same month Dynamic

maxgro — Wed, 02 Nov 2016 18:40:29 GMT

Your expression seems ok to me

count(
{<MonthYearID={$(=max({<FutureFlag={0}>}MonthYearID)-24)},FutureFlag={0}>}
Value)

maybe you have to disregard other selections? year, month, etc....?

or you can start simple without the FutureFlag=0 filter and check if it works

or post a small .qvw

Re: Prior Year same month Dynamic

jeckstein — Wed, 02 Nov 2016 18:48:02 GMT

Maxgro,

This could the issue. When I filter on "MonthYear" it does not seem to be working, But when I filter on "MonthYearID".

This is the code in my master calendar:

Date(monthstart(TempDate),'MMM-YY') as MonthYear,
AutoNumber(Date(monthstart(TempDate),'MMM-YY')) as MonthYearID,

Any idea how to fix this, so I can filter on "MonthYear"

Thanks,

Re: Prior Year same month Dynamic

maxgro — Wed, 02 Nov 2016 18:56:18 GMT

count(
{<MonthYearID={$(=max({<FutureFlag={0}>}MonthYearID)-24)},FutureFlag={0},MonthYear=>}
Value)