Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

sassoonj
New Contributor III

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

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!

1 Solution

Accepted Solutions
sassoonj
New Contributor III

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

Finally found it! here it is:

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

11 Replies
undergrinder
Valued Contributor II

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

Hi Jacob,

the backgroundcolor has an expression as well.

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

G.

sassoonj
New Contributor III

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

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
Valued Contributor II

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

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.

sassoonj
New Contributor III

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

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
Valued Contributor II

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

Could you share your expression with us?

sassoonj
New Contributor III

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

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
Valued Contributor II

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

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.

sassoonj
New Contributor III

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

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

sassoonj
New Contributor III

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

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.

Community Browser