Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jeckstein
Partner - Creator
Partner - Creator

Prior Year same month Dynamic

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.

5 Replies
sunny_talwar

May be like this:

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

jeckstein
Partner - Creator
Partner - Creator
Author

Sunny,

That does not seem to work

maxgro
MVP
MVP

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

jeckstein
Partner - Creator
Partner - Creator
Author

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,

maxgro
MVP
MVP

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