cell background colour by cell value for latest 3 months

[cloud]

Hi,

Pretty new to Qlikview, need some help

I have data consisting of Types, RDate, and Count. For example:

Type RDate Count
A 31/01/2019 25
A 28/02/2019 9
A 30/03/2019 65

B 31/01/2019 200

B 28/02/2019 4

B 30/03/2019 75

C 31/01/2019 14

C 28/02/2019 1

C 30/03/2019 20

So I have a different types, with the same 3 dates and 3 different counts for each date

These dates were derived from this expression for retrieving the latest 3 months (not sure if this expression is correct) as my excel file contains years worth of data.

Sum({$<RDATE>=$(=MonthStart(AddMonths(Today(), - 3)))&lt;$(=MonthEnd(AddMonth(Today())))"}&gt;} Count)

Displaying all these data on a Pivot Table, I would need to colour the background cells based on the cell value.

For example, for A, if count is more than 30 then red, below 10 then green but for B if count is more than 45 then red, below 20 then green, for C if count is more than 50 then red, below 16 is green. 

Means that each type has a different condition that colours the cell. How do I go about doing this?

Can't upload any pictures or files as the actual work contains sensitive information. I appreciate all the help I can get! Thanks!

[Shubham_Deshmukh · Accepted solution]

First create variables for this like:

vThreeMonthsBack : =MonthStart(AddMonths(Today(), - 3))

vMonthEnd : = MonthEnd(today())

Use this :

=sum({<dates ={">=$(=vThreeMonthsBack)<=$(=vMonthEnd)"}>}Count)

For background color, go to properties -> Visual Cues -> Give conditions like below,

[jyothish8807]

Hi Cloud,

You can write something like this in background expression 

[jyothish8807 · Accepted solution]

if(Type= 'A' and Count >20,red(), if(Type='A' and Count<=10,green(),
if(Type= 'B' and Count >20,red(), if(Type='B' and Count<=10,green(),
if(Type= 'C' and Count >20,red(), if(Type='C' and Count<=10,green()
))))))

[cloud]

hi shubham,

Your variables worked well for me, however I still don't understand how to colour the cell based by type with different conditions for each type. Do read my post again as I've updated my example. 

[cloud]

hi KC, 

Your solution did not work for me, the count derived is not as easy from sum(count), but the expression that retrieves the latest 3 months' data. I've tried using your expression but it does not show any colour.

[Shubham_Deshmukh]

Thanks  @cloud !!

If you want it type wise then @jyothish8807  solution will work,  working for me also,

=if(Company='A' and count >30,red(),
if(Company='A' and count <10,green(),.....))

-Shubham

[cloud]

hi shubham, 

It does not work for me. I encounter these problems:

Type A doesn't show any colour (supposed to show red),   Type B show the wrong colour (supposed to be red but show green) 

I've check my expression over and over again and I didn't put any range that does not cover any numbers 

Is it because of the latest 3 month expression? 

[Shubham_Deshmukh]

You want to show TYPE's background color or expression's background color?
Because above solutions will give expression's color like sum(count).

[cloud]

hi shubham,

it's the result values of the latest 3 months expression of each types that I want to change the background colour of.