Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
March 11, 2PM EST: Do More with Qlik - Getting Started wtih Qlik Sense SaaS - Redux. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bijoylalu007
Partner
Partner

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
Partner

Have a look at attached app.

View solution in original post

7 Replies
OmarBenSalem
Partner
Partner

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
Partner

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

shraddha_g
Partner
Partner

Have a look at attached app.

View solution in original post

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
Partner

Dear Shraddha,

This fits in my exact requirement.

Really Appreciated

Regards

Bijoy

shraddha_g
Partner
Partner

Glad it helped!