Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
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
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;

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

Capture.JPG

Highlighted
Contributor III
Contributor III

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 AUMPast Due360+
Next 10 Days = 25%180-360
Next 10 Days = 50%
GMMProduct FamilyProduct RemapDeal BankerCurrent SpecialistPipeline #Rel IDRelationship NameMember NameProduct TypeProduct NameCreated OnProjected Close Date Open Days Open Day Range Probability FO TrackingFinancial InstitutionInitiative CNR ($M)  AUM ($MM) 
Region 1BankingBankingBanker 1BankingP-00001AR0001Rel1Client 1Product type1Product name 19/2011/30/201732<6025n/an/an/a0               100
Region 2BankingBankingBanker 2BankingP-00002AR0002Rel2Client 2Product type2Product name 210/1711/30/20175<6050n/an/an/a312                        60
Region 3BankingBankingBanker 3BankingP-00003AR0003Rel3Client 3Product type3Product name 312/811/15/2017319180-36075Existingn/an/a150                 50
Region 4BankingBankingBanker 4BankingP-00004AR0004Rel4Client 4Product type4Product name 410/1010/31/201712<6050Existingn/an/a125                 50
Region 5BankingBankingBanker 5BankingP-00005AR0005Rel5Client 5Product type5Product name 52/19/29/2017264180-36025n/an/an/a0                 50
Region 6BankingBankingBanker 6BankingP-00006AR0006Rel6Client 6Product type6Product name 69/272/28/201825<6050n/an/an/a251                 50
Region 7BankingBankingBanker 7BankingP-00007AR0007Rel7Client 7Product type7Product name 78/1111/30/20177360-9025n/an/an/a25                 25
Region 8BankingBankingBanker 8BankingP-00008AR0008Rel8Client 8Product type8Product name 89/811/30/201745<6050n/an/an/a40                 25

   

Kind regards,
Esther
Highlighted
Contributor III
Contributor III

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

   

Past Due360+
Next 10 Days = 25%180-360
Next 10 Days = 50%
Current SpecialistProjected Close Date Open Days Open Day Range Probability
Specialist111/30/201732<6025
Specialist211/30/20175<6050
Specialist311/15/2017319180-36075
Specialist410/31/201712<6050
Specialist59/29/2017264180-36025
Specialist62/28/201825<6050
Specialist711/30/20177360-9025
Specialist811/30/201745<6050
Specialist911/30/201730<6050
Specialist1012/31/201727<6050
Specialist1111/30/201726<6050
Specialist1210/20/20175 <60 75
Specialist133/31/2018266180-36025