How do you count specific Colored results in a table

[deerhunter]

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]

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]

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]

Perfect that worked, Thanks

[seanbruton]

helpful

[seanbruton]

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]

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?