Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution.
**READ ALL ABOUT IT!**

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- App Development
- :
- Colour Expression for Budget Variance

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Not applicable

2017-08-10
07:57 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Colour Expression for Budget Variance

Hello,

I would like some help in formulating a text colour expression.

I want the text to be green when the variance ([Project Year Budget]) - sum([Project Expenditure Amount]) is less than the budget. But I also want the text to be red when the variance falls below zero. And, yellow when the variance is equal to the budget.

I have tried to do so using this expression:

if((sum([Project Year Budget]) - sum([Project Expenditure Amount])) < sum([Project Year Budget]), green (255)

,if((sum([Project Year Budget]) - sum([Project Expenditure Amount])) <'-1', red ()

,if((sum([Project Year Budget]) - sum([Project Expenditure Amount])) = sum([Project Year Budget]), RGB(255,128,0)

)))

The yellow and green text is working correctly. But I cannot work out how to get variance below zero to turn red. I imagine it is because a value below zero is also less than the project year budget and thus it turns green as it is commanded to do in the first line of the expression.

How would I get it to work?

Thank you in advance

1,181 Views

1 Solution

Accepted Solutions

dan_sullivan

Creator II

2017-08-10
08:26 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

IF( Sum([Project Expenditure Amount]) > Sum([Project Year Budget]) , RED(),

IF( Sum([Project Expenditure Amount]) < Sum([Project Year Budget]) , GREEN(),YELLOW()

))

the way you wrote the green condition won't this always be true assuming there is any spend?

sum([Project Year Budget]) - sum([Project Expenditure Amount]) < sum([Project Year Budget])

Can you elaborate on which results are not working?

1,055 Views

6 Replies

dan_sullivan

Creator II

2017-08-10
07:59 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

It will evaluate in order of the IF statement. So make the first check be the condition for RED, if that is met then all other conditions will not be checked. The move on from there with the second check.

1,055 Views

Not applicable

2017-08-10
08:04 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi Dan,

Thanks for your response.

Do you mean like this?

if((sum([Project Year Budget]) - sum([Project Expenditure Amount])) <0, red (255)

,if((sum([Project Year Budget]) - sum([Project Expenditure Amount])) < sum([Project Year Budget]), green (255)

,if((sum([Project Year Budget]) - sum([Project Expenditure Amount])) = sum([Project Year Budget]), RGB(255,128,0)

)))

If so, unfortunately that doesn't work.

1,055 Views

dan_sullivan

Creator II

2017-08-10
08:26 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

IF( Sum([Project Expenditure Amount]) > Sum([Project Year Budget]) , RED(),

IF( Sum([Project Expenditure Amount]) < Sum([Project Year Budget]) , GREEN(),YELLOW()

))

the way you wrote the green condition won't this always be true assuming there is any spend?

sum([Project Year Budget]) - sum([Project Expenditure Amount]) < sum([Project Year Budget])

Can you elaborate on which results are not working?

1,056 Views

Not applicable

2017-08-10
08:30 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi,

I have tried that but unfortunately it isn't working.

Using the expression in my initial post, it is only making the negative values red that is not working.

1,055 Views

dan_sullivan

Creator II

2017-08-10
08:58 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

not sure why it isn't working. can you post some data or a sample qvf to work from?

1,055 Views

Not applicable

2017-08-10
09:15 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi Dan,

As I cannot share the data with you, I was just making a test app to send to you but your expression worked for it. Strange! I am unsure what is causing the issue on my end in that case. But, thank you, your expression works.

1,055 Views

Community Browser