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]

CAn you share what you have written for background color?

[cloud]

hi shubham,

if(type = 'A' and Count < 17, LightGreen(),

if(type = 'A' and Count > 24, LightRed(),

if(type = 'B' and Count < 9, LightGreen(),

if(type = 'B' and Count > 14, LightRed(), 

if(type = 'C' and Count < 9, LightGreen(),

if(type = 'C' and Count > 14, LightRed()

))))))

The values of type A show no colour (values: 27, 65, 100), values of type B show correct all green colours  (values: 5,4,5), values of type C show wrong all green supposed to be red (values: 27,28,30)

[jyothish8807 · Accepted solution]

Hi Cloud,

Replace 'Count ' with your actual expression (The one with last 3 month logic), it should work then.

[cloud]

hi KC,
it finally worked! thank you so much
thank you shubham for your help too

[Shubham_Deshmukh]

Kudos !!

[jyothish8807]

You are welcome 🙂