topic Background colours for dates not working in QlikView

Background colours for dates not working

tinkerz1 — Wed, 09 Dec 2015 08:55:29 GMT

Hi,

I use the below code to make the date dimension in a pivot table, but the following code for coloring the backgorund for dates only works on the first 2 dates, what could be happening not to color the full selection?

=if(Num([Download Month-Year])>=num(MONTHSTART(Min_Date)) and Num([Download Month-Year])<=num(MONTHEND(Max_Date)),[MonthYear])

=if(Num([Download Month-Year])>=num(MONTHSTART(Min_Date)) and Num([Download Month-Year])<=num(MONTHEND(Max_Date)),Yellow())

Re: Background colours for dates not working

tyagishaila — Wed, 09 Dec 2015 09:16:46 GMT

could you please share screen shot to know that exactly what happening.

Re: Background colours for dates not working

roger_stone — Wed, 09 Dec 2015 09:21:29 GMT

You have only supplied a colour (yellow) when the IF statement is true, but no colour for false.

Re: Background colours for dates not working

tinkerz1 — Wed, 09 Dec 2015 10:06:02 GMT

For information this is the new background code:

=if(Num([Download Month-Year])>=num(MONTHSTART(Min_Date)) and Num([Download Month-Year])<=num(MONTHEND(Max_Date)),Yellow(),red())

But the dates stayed white.

Re: Background colours for dates not working

Anonymous — Wed, 09 Dec 2015 10:27:43 GMT

what is the max value of your date field??

Re: Background colours for dates not working

roger_stone — Wed, 09 Dec 2015 10:28:03 GMT

Looking at that screenshot, I think this may have more to do with nulls in your data.

Re: Background colours for dates not working

Anonymous — Wed, 09 Dec 2015 10:29:34 GMT

From your background code, and attached image:

=if(Num([Download Month-Year])>=num(MONTHSTART(Min_Date)) and Num([Download Month-Year])<=num(MONTHEND(Max_Date)),Yellow(),red())

It seems the max value of Date Field is Oct 2015 only, is it??

Re: Background colours for dates not working

tinkerz1 — Wed, 09 Dec 2015 10:36:11 GMT

=MONTHEND(Max_Date) is 29/02/2016

Re: Background colours for dates not working

tyagishaila — Wed, 09 Dec 2015 11:01:33 GMT

you can try it;

right click on dimension --->go to Custom format cell ,

from here you can provide background colour as per your requirement.

Re: Background colours for dates not working

tinkerz1 — Wed, 09 Dec 2015 11:18:56 GMT

Thanks but then I will have to do that every time I run a report for a new month

Re: Background colours for dates not working

tyagishaila — Wed, 09 Dec 2015 11:32:01 GMT

not getting you. why you have to do every time.

background color is a chart property..

Its independent from expression value.

if possible share your qvw file.

Re: Background colours for dates not working

tinkerz1 — Wed, 09 Dec 2015 12:17:21 GMT

I sorry I cant can't change send the qvw.

At the moment the date range is 9 months but the user can set the date range to what ever they choose, so coloring specfic cells wont work properly if the date range changes.

Thanks.

Re: Background colours for dates not working

tinkerz1 — Wed, 09 Dec 2015 12:43:36 GMT

Ok solved it, it is a case of unchecking all surpressing values and then looking at the table to only check the surpressing values you need.