Countif with a Master Item Dimension as condition

JFDemers — Wed, 14 Jun 2023 15:29:39 GMT

Hi,

I am currently loosing my mind on something that seems so simple. I need to do a conditional count based on a single condition. My issue is that Qlik seems to not properly handle the fact that my condition is on a Master Item Dimension

Master Item (Dimension)
Name: Performance Status YTD
Exppression:

=if(

aggr(

([Account Return YTD]-[Benchmark Return YTD]), AccountCode)<0,'Underperforming','Outperforming'

)

Master Item (Measure) --> the one that needs the countif

Name: # Underperforming Portfolios

Expression:

COUNT({<Performance Status YTD={'Underperforming'}>} DISTINCT AccountCode)

*** For some reason I am not allowed to use Dimension in the formula as the autocomplete does not suggest the Dimension

So I tried creating a Master Item (Measure) instead and use the Measure in the formula:

Master Item (Measure) --> the one that needs to go inside the counif

Name: Performance Status YTD

Expression:

if(

aggr(

([Account Return YTD]-[Benchmark Return YTD]), AccountCode)<0,'Underperforming','Outperforming'

)

My second try on the # Underperforming Portfolios Measure

COUNT({<[Performance Status YTD]={'Underperforming'}>} DISTINCT AccountCode)

Even though the autocomplete suggest me the new Measure as expected, the result is not returning the proper value. It returns the total of all distinct Account Code as if the condition is not working.

I must be missing something but simply cannot figure it out...

Thx in advance!

topic Countif with a Master Item Dimension as condition in App Development

Countif with a Master Item Dimension as condition