Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cloud
Contributor III
Contributor III

cell background colour by cell value for latest 3 months

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!

Labels (4)
3 Solutions

Accepted Solutions
Shubham_Deshmukh
Specialist
Specialist

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,fdfdfd.png

View solution in original post

jyothish8807
Master II
Master II

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

Best Regards,
KC

View solution in original post

jyothish8807
Master II
Master II

Hi Cloud,

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

Best Regards,
KC

View solution in original post

15 Replies
Shubham_Deshmukh
Specialist
Specialist

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,fdfdfd.png

jyothish8807
Master II
Master II

Hi Cloud,

You can write something like this in background expression 

Capture.PNG

Best Regards,
KC
jyothish8807
Master II
Master II

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

Best Regards,
KC
cloud
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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

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
Contributor III
Contributor III
Author

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

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
Contributor III
Contributor III
Author

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.