I have an app that covers multiple species and presents a number of KPIs for each species. I'm not developing part of this that will display all the same KPIs but specific to each species. This is a requirement of the business so I can't avoid it.
I'm using set analysis in my KPIs and it is working, my pivot table is now only showing Dog data. However I know have a factor that uses a different formula depending on whether something is the latest month or older than that. Once I drop this IF formula in (which works fine in my "All Species" sheets) the other species appear but the data is blanked.
I've isolated the problem down to the IF part, but I can't see how you could alter this to retain this "permanent filter":
Formula 1 - Blanking out the latest month record:
If ([Datestamp.autoCalendar.YearMonth] <= AddMonths(Monthend(Today()),-2),
1 - Count({<$(vRetentionDay)={$(vRetentionDay)},Species={Dog}>} Policy_No) / (Count({<Species={Dog}>}Policy_No) - Count({<UnRenewal={'Y'},Species={Dog}>} Policy_No))
Formula 2 - Not blanking latest month record:
1 - Count({<$(vRetentionDay)={$(vRetentionDay)},Species={Dog}>} Policy_No) / (Count({<Species={Dog}>}Policy_No) - Count({<UnRenewal={'Y'},Species={Dog}>} Policy_No))
AND the IF that ruins it by then displaying every species again:
If ($(vRetentionDay) = '31d'
,
If ([Datestamp.autoCalendar.YearMonth] <= AddMonths(Monthend(Today()),-2),
1 - Count({<$(vRetentionDay)={$(vRetentionDay)},Species={Dog}>} Policy_No) / (Count({<Species={Dog}>}Policy_No) - Count({<UnRenewal={'Y'},Species={Dog}>} Policy_No)))
,
1 - Count({<$(vRetentionDay)={$(vRetentionDay)},Species={Dog}>} Policy_No) / (Count({<Species={Dog}>}Policy_No) - Count({<UnRenewal={'Y'},Species={Dog}>} Policy_No)))
Apologies if I've pasted one too many or few parentheses, I've copied and pasted this so it does function, just not the filtering aspect.
Any help would be appreciated.
