Hi,
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 Date | Project ID | Status |
|---|
| Jan 2017 | A | Yellow |
| Feb 2017 | A | Green |
| Mar 2017 | A | Yellow |
| Jan 2017 | B | Green |
| Feb 2017 | B | Green |
And having Report Date as my dimension in the chart, the chart should have something like
| Report Date | Green Count |
|---|
| Jan 2017 | 1 (*because only Project B reported Green) |
| Feb 2017 | 2 (*because both Project A and B reported Green) |
| Mar 2017 | 1 (*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 Date | Green Count |
|---|
| Jan 2017 | 0 |
| Feb 2017 | 1 |
| Mar 2017 | 1 |
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.
