
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Finally found it! here it is:
=count({$<[Reporting Date]={">=$(=Addmonths(max({1}[Reporting Date]),-5))"}>}aggr($(vColorIndex),[Reporting Month-Year],Entity))


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Jacob,
the backgroundcolor has an expression as well.
You should see that expression, and customize it in order to use at gauge.
G.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I see the problem, you want to filter that data.
The AddMonths function will help you identify the last 6 months,
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Could you share your expression with us?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 ))


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Not working unfortunately. The error I'm getting reads "Error in Expression: Nested aggregation not allowed"

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- « Previous Replies
-
- 1
- 2
- Next Replies »