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]

Hi Jacob,

the backgroundcolor has an expression as well.

You should see that expression, and customize it in order to use at gauge.

G.

[Anonymous]

Exactly, I created the background color expressions. But not quite sure how to customize them for my needs. How can I count the for the last 6 months and then for months 7-12?

[undergrinder]

I see the problem, you want to filter that data.

The AddMonths function will help you identify the last 6 months,

https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/Scripting/DateAndTimeFunctions/add...

then paste it in a set expression,

something like this:

=SUM({<Date={"$(>=AddMonth(Max(Date,-6))"}>} ValueToBeSummed)

It is not tested, just the logic should be similar.

G.

[Anonymous]

Still trying to figure this one out but not quite getting it.

I've used the expression in the background color to get me the value I want for the current month, but still can't figure out the right expression to sum for the most recent 6. I used the example you provided but had no luck.

[undergrinder]

Could you share your expression with us?

[Anonymous]

Sure, here it is. It' a bit lengthy

if(((((Sum([Active Clients Low Risk])/sum([Active Clients])*(1/10))
+
(Sum([Active Clients Medium Risk])/sum([Active Clients])*(2/10))
+
(Sum([Active Clients Moderate-High Risk])/sum([Active Clients])*(3/10))
+
(Sum([Active Clients High Risk])/sum([Active Clients])*(4/10)))
-(1/10))
/((4/10-(1/10)))*100)

/

(rangeavg(

((((Sum([Active Clients Low Risk])/sum([Active Clients])*(1/10))
+
(Sum([Active Clients Medium Risk])/sum([Active Clients])*(2/10))
+
(Sum([Active Clients Moderate-High Risk])/sum([Active Clients])*(3/10))
+
(Sum([Active Clients High Risk])/sum([Active Clients])*(4/10)))
-(1/10))
/((4/10-(1/10)))*100)

,0,12)

/10)>1.2
or
((((Sum([Active Clients Low Risk])/sum([Active Clients])*(1/10))
+
(Sum([Active Clients Medium Risk])/sum([Active Clients])*(2/10))
+
(Sum([Active Clients Moderate-High Risk])/sum([Active Clients])*(3/10))
+
(Sum([Active Clients High Risk])/sum([Active Clients])*(4/10)))
-(1/10))
/((4/10-(1/10)))*100)

/

(rangeavg(

((((Sum([Active Clients Low Risk])/sum([Active Clients])*(1/10))
+
(Sum([Active Clients Medium Risk])/sum([Active Clients])*(2/10))
+
(Sum([Active Clients Moderate-High Risk])/sum([Active Clients])*(3/10))
+
(Sum([Active Clients High Risk])/sum([Active Clients])*(4/10)))
-(1/10))
/((4/10-(1/10)))*100)

,0,12)

/10)<.8,
3,

if (((((Sum([Active Clients Low Risk])/sum([Active Clients])*(1/10))
+
(Sum([Active Clients Medium Risk])/sum([Active Clients])*(2/10))
+
(Sum([Active Clients Moderate-High Risk])/sum([Active Clients])*(3/10))
+
(Sum([Active Clients High Risk])/sum([Active Clients])*(4/10)))
-(1/10))
/((4/10-(1/10)))*100)

/

(rangeavg(

((((Sum([Active Clients Low Risk])/sum([Active Clients])*(1/10))
+
(Sum([Active Clients Medium Risk])/sum([Active Clients])*(2/10))
+
(Sum([Active Clients Moderate-High Risk])/sum([Active Clients])*(3/10))
+
(Sum([Active Clients High Risk])/sum([Active Clients])*(4/10)))
-(1/10))
/((4/10-(1/10)))*100)

,0,12)

/10)>.9
and
((((Sum([Active Clients Low Risk])/sum([Active Clients])*(1/10))
+
(Sum([Active Clients Medium Risk])/sum([Active Clients])*(2/10))
+
(Sum([Active Clients Moderate-High Risk])/sum([Active Clients])*(3/10))
+
(Sum([Active Clients High Risk])/sum([Active Clients])*(4/10)))
-(1/10))
/((4/10-(1/10)))*100)

/

(rangeavg(

((((Sum([Active Clients Low Risk])/sum([Active Clients])*(1/10))
+
(Sum([Active Clients Medium Risk])/sum([Active Clients])*(2/10))
+
(Sum([Active Clients Moderate-High Risk])/sum([Active Clients])*(3/10))
+
(Sum([Active Clients High Risk])/sum([Active Clients])*(4/10)))
-(1/10))
/((4/10-(1/10)))*100)

,0,12)

/10)<1.1,
1,

2 ))

[undergrinder]

Yes, it is a bit complicated

As I see the expression outcome can be 1,2 or 3.

I encourage you to make intermediate columns, if it is possible.

I think with set analysis it will be more difficult.

With simple function:

count the result of 1 in last 6 months:

sum(if(YourDate>Addmonth(max(YourDate),-6) AND [yourAboveExpression]=1,1,0))

G.

[Anonymous]

Not working unfortunately. The error I'm getting reads "Error in Expression: Nested aggregation not allowed"

[Anonymous]

Looks like I need to create a variable from my original expression and then have to use the Aggr function. I've created the variable but not sure how to use the Aggr function in this context.