topic How do you count specific Colored results in a table in App Development

How do you count specific Colored results in a table

deerhunter — Thu, 18 Jan 2018 03:25:17 GMT

Trying to write a KPI that shows the number of rows returned where the background color was changed to Red based on a condition.

This is the FX:

If ( TOTAL_TAX > Avg(TOTAL <[Category Type]> TOTAL_TAX) , red() , green() )

Running Qlik Sense November Release desktop

Thanks in advance

petter — Thu, 18 Jan 2018 05:49:52 GMT

I think this should work for you:

Sum( Aggr ( -(Sum(TOTAL_TAX) > Avg(TOTAL <[Category Type]> TOTAL_TAX)) , dim1 ) )

You have to replace the dim1 in the above expression with the same dimension(s) as in your chart.

If there is more than one dimension you have to list all with a comma in between instead of just dim1

pradosh_thakur — Thu, 18 Jan 2018 05:56:48 GMT

sum(If ( TOTAL_TAX > Avg(TOTAL <[Category Type]> TOTAL_TAX) , 1,0))

If you are using it in a KPI you may need to use something like this

sum(aggr(If ( TOTAL_TAX > Avg(TOTAL <[Category Type]> TOTAL_TAX) , 1,0),your dimesnions) )

deerhunter — Thu, 18 Jan 2018 13:06:44 GMT

Perfect that worked, Thanks

seanbruton — Thu, 18 Jan 2018 13:37:29 GMT

helpful

seanbruton — Fri, 19 Jan 2018 11:30:04 GMT

Buck,

I looked into this a bit and played with the idea.

On my dashboard, we just have the totals for various dimension/ measure relationships.

The thing that got me thinking was that a total is always the nett value of positive and negative numbers.

So, therefore, this post got my interest as its always good to show the movement in the business.

What I did was in my case I have Current YTD values and Previous YTD values the sum of the two is my variance.

So if Current is greater than Previous its green and the reverse is red. If equal yellow.

I wrote that if current > previous then 1, then sum of the ones, then to get a financial value I added this.

Sum(CYTD_Value* IF(CYTD_Value > PYTD_Value,1,0)) so in theory this stays dynamic.

My results are great now as I have 3 KPI's

- Total Financial Value and number of accounts

- Total Increase or grown Financial Value and number of account doing better than last year.

- Total Decline or slipping Financial Value and number of accounts doing worse than last year.

I hope this helps.

deerhunter — Tue, 30 Jan 2018 01:38:07 GMT

Awesome, so if i also wanted to count those results where Sum(TOTAL_TAX) Plus 200 Greater than the Avg(TOTAL <[Category Type]> TOTAL_TAX))

How might that look?