Count of items in last 6 months and from 7-12 months ago

[Anonymous]

Hi,

I've got an interesting question that's been stumping me for a while. I have a table with background color expressions in each cell, think Heatmaps. Ideally what I'd like to do is count the number of cells in the first 4 rows that are green, amber or red and then assign that calculation as an expression in a gauge. I want to do this for the most recent 6 months and then again for 7-12 months ago.

Not sure if Qlik Sense Desktop can count background colors. If this is the case then what other options do I have? Any insight is greatly appreciated!

Thanks!

[undergrinder]

Unfortunately I can't test it.

I'm not sure you have to use Aggr, because we need the max of all date, not by dimension.

Be patient and try various form, I think it is going to work

[Anonymous · Accepted solution]

Finally found it! here it is:

=count({$<[Reporting Date]={">=$(=Addmonths(max({1}[Reporting Date]),-5))"}>}aggr($(vColorIndex),[Reporting Month-Year],Entity))