Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
bijoylalu007
Partner - Contributor II
Partner - Contributor II

Text Color Expression of a pivot with Coloring logics

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.

Current Result.JPG

Also attaching my expected result below (screenshot from excel)

Expected Result.JPG

Kindly advise. Thanks in advance.

1 Solution

Accepted Solutions
shraddha_g
Partner - Master III
Partner - Master III

Have a look at attached app.

View solution in original post

7 Replies
OmarBenSalem

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

))))

vvira1316
Specialist II
Specialist II

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

shraddha_g
Partner - Master III
Partner - Master III

App you attached is giving zero values in 2016ACT & 2016BUD

shraddha_g
Partner - Master III
Partner - Master III

Have a look at attached app.

sumanta12
Creator II
Creator II

PIC3.PNG

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

))))

bijoylalu007
Partner - Contributor II
Partner - Contributor II
Author

Dear Shraddha,

This fits in my exact requirement.

Really Appreciated

Regards

Bijoy

shraddha_g
Partner - Master III
Partner - Master III

Glad it helped!