topic Re: Conditional Color on Pivotable in QlikView

Conditional Color on Pivotable

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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 member	Projected close date	Probability
Sales1	10/30/17	25
Sales2	11/05/17	25
Sales3	10/28	50

Desired result

Sales member	Projected close date	Probability
Sales1	10/30/17	25
Sales2	11/05/17	25
Sales3	10/28	50

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

Re: Conditional Color on Pivotable

devarasu07 — Wed, 25 Oct 2017 03:02:49 GMT

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;

LOAD * INLINE [

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

Re: Conditional Color on Pivotable

Anonymous — Wed, 25 Oct 2017 19:53:34 GMT

Hi,

Thank you for your response.

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

Banker 1

Banking

P-00001

AR0001

Rel1

Client 1

Product type1

Product name 1

9/20

11/30/2017

<60

n/a

100

Region 2

Banking

Banker 2

Banking

P-00002

AR0002

Rel2

Client 2

Product type2

Product name 2

10/17

11/30/2017

<60

n/a

312

Region 3

Banking

Banker 3

Banking

P-00003

AR0003

Rel3

Client 3

Product type3

Product name 3

12/8

11/15/2017

319

180-360

Existing

n/a

150

Region 4

Banking

Banker 4

Banking

P-00004

AR0004

Rel4

Client 4

Product type4

Product name 4

10/10

10/31/2017

<60

Existing

n/a

125

Region 5

Banking

Banker 5

Banking

P-00005

AR0005

Rel5

Client 5

Product type5

Product name 5

2/1

9/29/2017

264

180-360

n/a

Region 6

Banking

Banker 6

Banking

P-00006

AR0006

Rel6

Client 6

Product type6

Product name 6

9/27

2/28/2018

<60

n/a

251

Region 7

Banking

Banker 7

Banking

P-00007

AR0007

Rel7

Client 7

Product type7

Product name 7

8/11

11/30/2017

60-90

n/a

Region 8

Banking

Banker 8

Banking

P-00008

AR0008

Rel8

Client 8

Product type8

Product name 8

9/8

11/30/2017

<60

n/a

Kind regards,

Esther

Re: Conditional Color on Pivotable

Anonymous — Wed, 25 Oct 2017 19:58:00 GMT

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