Qlik Community

Qlik Sense App Development

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

deerhunter
Contributor

How do you count specific Colored results in a table

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

Tags (1)
6 Replies
MVP
MVP

Re: How do you count specific Colored results in a table

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

Re: How do you count specific Colored results in a table

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) )

Learning never stops.
deerhunter
Contributor

Re: How do you count specific Colored results in a table

Perfect that worked, Thanks

Luminary
Luminary

Re: How do you count specific Colored results in a table

helpful

Luminary
Luminary

Re: How do you count specific Colored results in a table

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
Contributor

Re: How do you count specific Colored results in a table

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?

Community Browser