Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys
I have a pivot table and I wish to set conditional background colours , but I need to set it on multiple selections,
Basically if
Field [Report] = CBIRE AND [Total] < 70 go Green
Field [Report] = CBIRE AND [Total] < 110 go Orange
Field [Report] = CBIRE AND [Total] > 70 go Red
AND
Field [Report] = ROI AND [Total] < 250 go Green
Field [Report] = ROI AND [Total] < 500 go Orange
Field [Report] = ROI AND [Total] > 500 go Red
I actually have another 4 rules based on different options in Report and Total to add in. But I have tried the first part using
if(Report='CBINT' and Total <=70,RGB (0,255,0),if(Report='CBINT' and Total<=110,RGB (255,255,0),RGB (255,0,0)))
but this is turning them all red regardless of what is filtered or not.
Can anyone please help ?
Thanks
A
Working with both none or one selected...
Haha OK thats it Ive officially given up Its genuinely not happening for me
See Fig 1 In total column I would have wanted Jan ,Feb , Mar and May to be Green as they are less that 920 and only April Red.
Fig 2 shows that when i drill into a Report it works fine !!!
I've literally gone crazy looking at this
Below is the exact condition.
Thanks so much for your time and help Stigchel ,
if(Report='CM Banking INT' and [3.1.3 Reconciliations - total number of outstanding items]<=600,RGB (0,127,0), |
if(Report='CM Banking INT' and [3.1.3 Reconciliations - total number of outstanding items]<=800,RGB (255,128,0), |
if(Report='CM Banking INT' and [3.1.3 Reconciliations - total number of outstanding items]>800,RGB (255,0,0), |
if(Report='PAYMENTS/I&I' and [3.1.3 Reconciliations - total number of outstanding items]<=70,RGB (0,127,0), |
if(Report='PAYMENTS/I&I' and [3.1.3 Reconciliations - total number of outstanding items]<=110,RGB (255,128,0), |
if(Report='PAYMENTS/I&I' and [3.1.3 Reconciliations - total number of outstanding items] >110,RGB (255,0,0), |
if(Report='IRE' and [3.1.3 Reconciliations - total number of outstanding items]<=50,RGB (0,127,0), |
if(Report='IRE' and [3.1.3 Reconciliations - total number of outstanding items]<=75,RGB (255,128,0), |
if(Report='IRE' and [3.1.3 Reconciliations - total number of outstanding items]>75,RGB (255,0,0), |
if(Report='AIB Bank/AIB(UK)' and [3.1.3 Reconciliations - total number of outstanding items]<=250,RGB (0,127,0), |
if(Report='AIB Bank/AIB(UK)' and [3.1.3 Reconciliations - total number of outstanding items]<=500,RGB (255,128,0), |
if(Report='RAIB Bank/AIB(UK)' and [3.1.3 Reconciliations - total number of outstanding items]>500,RGB (255,0,0), |
if(GetSelectedCount(Report)=0 and [3.1.3 Reconciliations - total number of outstanding items]<=920,RGB (0,127,0), |
if(GetSelectedCount(Report)=0 and [3.1.3 Reconciliations - total number of outstanding items]<=1406,RGB (255,128,0),RGB (255,0,0))))))))))))))) |
Hi there
I have resigned myself to the fact that I will not get the conditional colors on the totals ,
So I have reverted back to your original suggestion Stigchel, in the script I put an if statement
if([3.1.3 Reconciliations - total number of outstanding items]<70,'Green',if([3.1.3 Reconciliations - total number of outstanding items]>70 and [3.1.3 Reconciliations - total number of outstanding items]<110,'Orange','Red')) as [RAG Code],
So created a column that determines value based on Totals field, then using this column ( RAG Code) wrote the following condition into my expression
if([RAG Code]='Green' and Report='CM Banking INT' |
or [RAG Code]='Green' and Report='PAYMENTS/I&I' |
or [RAG Code]='Green' and Report='AIB Bank/AIB(UK)' |
or [RAG Code]='Green' and Report='IRE', RGB(0,127,0), |
if([RAG Code]='Orange' and Report='CM Banking INT' |
or [RAG Code]='Orange' and Report='PAYMENTS/I&I' |
or [RAG Code]='Orange' and Report='AIB Bank/AIB(UK)' |
or [RAG Code]='Orange' and Report='IRE', RGB (255,128,0), |
if([RAG Code]='Red' and Report='CM Banking INT' |
or [RAG Code]='Red' and Report='PAYMENTS/I&I' |
or [RAG Code]='Red' and Report='AIB Bank/AIB(UK)' |
or [RAG Code]='Red' and Report='IRE', RGB (255,0,0), |
RGB(255,255,255)))) |
So the colors will only change when I select a report , failing that they will stay white, its not ideal but its a workaround.
Cant thank you enough for all your help and time
Anne