Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

New Contributor

Set Analysis Exclusion in Max Function in Same Dimension


I'm new to QV and I've been struggling with this problem for two days now and I hope someone can help me quickly.

I have the following expression in one of my charts:

Count(If(Aggr(NODISTINCT max( [Report Date]), [Project ID]) = [Report Date], if([Project Health]='Green',[Project ID])))

The context here is that I am attempting to count the number of projects with "Green" status based on the last reported status.

So for example, given the following data:

Report DateProject IDStatus
Jan 2017AYellow
Feb 2017AGreen
Mar 2017AYellow
Jan 2017BGreen
Feb 2017BGreen

And having Report Date as my dimension in the chart, the chart should have something like

Report DateGreen Count
Jan 20171 (*because only Project B reported Green)
Feb 20172 (*because both Project A and B reported Green)
Mar 20171 (*because the last value of Project B was Green while the last reported value of Project A is Yellow)

My problem is that the max([Report Date]) part is evaluating the maximum report date for each Project ID and not only up to the value in the dimension so what I'm getting instead is

Report DateGreen Count
Jan 20170
Feb 20171
Mar 20171

What I want to do:

I want to modify the Max function expression to only report the maximum value of [Report Date] that is equal to or less than the dimension value of [Report Date] being evaluated in the chart, but I don't know how to do this via set analysis or anything else.

Please help.