Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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
Highlighted

May be like this:

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

Highlighted
Partner
Partner

Sunny,

That does not seem to work

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

Highlighted
Partner
Partner

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,

Highlighted
MVP
MVP

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