# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for
Did you mean:
Highlighted
Contributor III

## Conditional Color on Pivotable

Hello,

I was wondering if I could get some help with QlikView please.

I would like to apply conditional color to a pivot table using the following criteria: Highlight "Projected close date" field yellow if the current date ("now" function) subtract 1 (current date not included) and subtracted from the dates within the field is less than ten days. So basically if there are 10 days remaining between the current date and the list of dates in the projected close date field. There is also one more criteria, it should be equal to 25 under the probability field in the table.

The second criteria is highlight orange if the present day (now) subtract 1 (current date is not included) subtracted from the projected close dates field is less than 10 and equal to 50 in the probability field.

Summary

Next 10 days = 50%  (now subtract 1 subtract projected close date Is equal to 10 and 25% probability)

Next 10 days = 25% (now subtract 1 subtract projected close date Is equal to 10 and 50% probability)

Past due =<0 days, (now subtract 1 subtract projected close date Is less than 0, % is not relevant)

Sample

Sales memberProjected close dateProbability
Sales110/30/1725
Sales211/05/1725
Sales310/2850

Desired result

Sales memberProjected close dateProbability
Sales110/30/1725
Sales211/05/1725
Sales310/2850

Finally highlight red id the present day and the number of days to close is 0 or greater.

Unrelated to the above formula one of my dimension fields is displaying 10.00463746 and I would like to know how to format it to just 10 please.

Many thanks,

Esther

3 Replies
Highlighted
Master II

Hi,

Refer to the attachment, can you provide the mock data for the probability logic (current sample not suite)

update your date format like below

SET DateFormat='M/D/YY';

then find the date diff from current date,

Fact:

load *,Interval(Date(Today(),'DD/MM/YY'),'DD/MM/YY')-Date([Projected close date],'d') as DateDiff;

Sales member, Projected close date, Probability

Sales1,10/30/17, 25

Sales2,11/05/17, 25

Sales3,10/28/17, 50

];

the text color you write below condition statement (apply your probability logic here)

if([Sales member]='Sales1',Yellow(), if([Sales member]='Sales3',LightRed()))

Highlighted
Contributor III

Hi,

So you are suggesting that I list and combine the field names in the script editor like your example?

I have a lot of data so that will be quite time consuming

I assume you mean that the sample provided was not sufficient? Please see below a large sample from my data set.

 All open pipeline deals sorted by specialist and AUM Past Due 360+ Next 10 Days = 25% 180-360 Next 10 Days = 50% GMM Product Family Product Remap Deal Banker Current Specialist Pipeline # Rel ID Relationship Name Member Name Product Type Product Name Created On Projected Close Date Open Days Open Day Range Probability FO Tracking Financial Institution Initiative CNR (\$M) AUM (\$MM) Region 1 Banking Banking Banker 1 Banking P-00001 AR0001 Rel1 Client 1 Product type1 Product name 1 9/20 11/30/2017 32 <60 25 n/a n/a n/a 0 100 Region 2 Banking Banking Banker 2 Banking P-00002 AR0002 Rel2 Client 2 Product type2 Product name 2 10/17 11/30/2017 5 <60 50 n/a n/a n/a 312 60 Region 3 Banking Banking Banker 3 Banking P-00003 AR0003 Rel3 Client 3 Product type3 Product name 3 12/8 11/15/2017 319 180-360 75 Existing n/a n/a 150 50 Region 4 Banking Banking Banker 4 Banking P-00004 AR0004 Rel4 Client 4 Product type4 Product name 4 10/10 10/31/2017 12 <60 50 Existing n/a n/a 125 50 Region 5 Banking Banking Banker 5 Banking P-00005 AR0005 Rel5 Client 5 Product type5 Product name 5 2/1 9/29/2017 264 180-360 25 n/a n/a n/a 0 50 Region 6 Banking Banking Banker 6 Banking P-00006 AR0006 Rel6 Client 6 Product type6 Product name 6 9/27 2/28/2018 25 <60 50 n/a n/a n/a 251 50 Region 7 Banking Banking Banker 7 Banking P-00007 AR0007 Rel7 Client 7 Product type7 Product name 7 8/11 11/30/2017 73 60-90 25 n/a n/a n/a 25 25 Region 8 Banking Banking Banker 8 Banking P-00008 AR0008 Rel8 Client 8 Product type8 Product name 8 9/8 11/30/2017 45 <60 50 n/a n/a n/a 40 25

Kind regards,
Esther
Highlighted
Contributor III

Sorry above data set was cut off, please see below.

 Past Due 360+ Next 10 Days = 25% 180-360 Next 10 Days = 50% Current Specialist Projected Close Date Open Days Open Day Range Probability Specialist1 11/30/2017 32 <60 25 Specialist2 11/30/2017 5 <60 50 Specialist3 11/15/2017 319 180-360 75 Specialist4 10/31/2017 12 <60 50 Specialist5 9/29/2017 264 180-360 25 Specialist6 2/28/2018 25 <60 50 Specialist7 11/30/2017 73 60-90 25 Specialist8 11/30/2017 45 <60 50 Specialist9 11/30/2017 30 <60 50 Specialist10 12/31/2017 27 <60 50 Specialist11 11/30/2017 26 <60 50 Specialist12 10/20/2017 5 <60 75 Specialist13 3/31/2018 266 180-360 25