Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
In my app, I want to set color for my Actual values using "Text Color expression" in a pivot table. In the attached application I want to apply color for Actual scenarios when my Scenario is always having ACT(Actuals). The color for the values denotes a comparison with Actual and Budget values.
Two types of accounts are there in my application (Revenue and Expense). All Revenue accounts are starting with '101' and all others are expense.
Requirement
1. When actual revenue is more than Budget then it should be in Green Color and if actual Revenue is less than Budget then Red color.
2. When actual expense is more than Budget then it should be in Red color and if actual expense is less than Budget then Green color.
I used the expression in the Text Color Expression field of pivot for this,but its not giving my expected result.
Current Result is shown below.
Also attaching my expected result below (screenshot from excel)
Kindly advise. Thanks in advance.
Have a look at attached app.
Maybe:sthing like
if(Account like "101*" and sum(Actual) >= sum(Budget), green(),
if(Account like "101*" and sum(Actual) < sum(Budget), red(),
if(Account like "20*" and sum(Actual) >= sum(Budget), red(),
if(Account like "20*" and sum(Actual) < sum(Budget), green()
))))
You may want to try similar to following with your required set analysis. As your QVF file doesn't have any data I couldn' test it.
If (AcctType='Revenue',
If(Sum( {<Set Analysis>} Actual) >= Sum( {<Set Analysis>} Budget), RGB(0,255,0), RGB(255,0,0)),
If(Sum( {<Set Analysis>} Actual) <= Sum( {<Set Analysis>} Budget), RGB(0,255,0), RGB(255,0,0)))
App you attached is giving zero values in 2016ACT & 2016BUD
Have a look at attached app.
TEXT COLOR EXPRESSION:
IF(WildMatch(ACCOUNT,'101*') AND SUM(ACT)>=SUM(BUD),RGB(0,255,0),
IF(WildMatch(ACCOUNT,'101*') AND SUM(ACT)<SUM(BUD),RGB(255,0,0),
IF(WildMatch(ACCOUNT,'20*') AND SUM(ACT)>=SUM(BUD),RGB(255,0,0),
IF(WildMatch(ACCOUNT,'20*') AND SUM(ACT)<SUM(BUD),RGB(0,255,0),
))))
Dear Shraddha,
This fits in my exact requirement.
Really Appreciated
Regards
Bijoy
Glad it helped!