topic Re: Multiple if statements under expression text color in QlikView

Multiple if statements under expression text color

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello all,

I have a question regarding the use of multiple IF statements.

I am working with 7 product groups, that I am displaying in the same graph. I am using a list box to toogle between each, showing their sales number in the graph.

My question is regarding the feature "Text color" under expression.

I am trying to add different colors depending on which of the 7 products i choose. What I want to do is that sale numbers above xx (depending on the choosen product) are shown in green, and below this number are shown in red. As the sale numbers vary a lot between the 7 product groups the "cut-off" point should be different for each, hence the IF statement.

I tried with the following:

=if( [sales] >='500' and [Product_Group] = '1',rgb(255,110,110),rgb(108,179,63),

if( [sales] >='500' and [Product_Group] = '2',rgb(255,110,110),rgb(108,179,63),

if( [sales] >='500' and [Product_Group] = '3 SW',rgb(255,110,110),rgb(108,179,63),

if( [sales] >='500' and [Product_Group] = '4 5D',rgb(255,110,110),rgb(108,179,63),

if( [sales] >='500' and [Product_Group] = '5',rgb(255,110,110),rgb(108,179,63),

if( [sales] >='500' and [Product_Group] = '6',rgb(255,110,110),rgb(108,179,63),

if( [sales] >='500' and [Product_Group] = '7',rgb(255,110,110),rgb(108,179,63)

)))))))

Again the reason for the IF statements is that the 500 should be different for each product.

I have only been using QlikView for less than a week, so I am sorry if my question and/or programmering is not very clear.

Thank you very much for your help!

Mathias

Re: Multiple if statements under expression text color

rupamjyotidas — Tue, 22 Nov 2016 14:25:31 GMT

I can see same color combination for all. If that's what you are looking. Then try only this.

=if( [sales] >='500' ,rgb(255,110,110),rgb(108,179,63))

Or if all of them are different, try something with 'Pick' and 'If Else'

if( [sales] >='500' ,Pick([Product_Group],RGB(Your Combination),RGB(Your Combination),......etc),rgb(108,179,63))

Hope this helps

Re: Multiple if statements under expression text color

julian_rodriguez — Tue, 22 Nov 2016 14:36:12 GMT

Hello Mathias

You need a table for the budget. The way that you are using is too complicated.

I'm attaching a example, that uses a table to get the budget information (for your case, it's the "cut-off" point)

Please note the datamodel and the color expression, there are the things that you could use.

Best regards

Julian

Re: Multiple if statements under expression text color

Anonymous — Tue, 22 Nov 2016 14:38:36 GMT

Hey Rupam, thanks for the fast reply!

Yes in what I posted the colors are listed as the same, which they also should be. But the "cutoff" point is what I am trying to set differently.

For example product group 1 has sales between 1 and 1000 (hypothetically), so above 500 should be green, and below red. But product group 2 has sales between 5000 and 10000, so if I have the same "cutoff" point at 500, all will be green. Instead I wanted this to be like above 7500. So every product should have same colors, but different "cutoff" points. So when I pick product 1 in the List box, it will have green and red for above and below 500, while for product 2 this would for example be above and below 7500

I hope this makes sense!

I will try to see if I can make something work with Pick and IF else.

Thank you very much

Re: Multiple if statements under expression text color

rubenmarin — Tue, 22 Nov 2016 14:43:10 GMT

Hi Mathias, the 'If' function syntaxis is: if(condition , then , else) https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/ConditionalFunctions/if.htm

You are trying to use a third paremeter, wich doesn't exist, so you'll need to adapt you requirements to the function syntaxis, so it can be:

If([Product_Group] = '1', // 'Condition'

If( [sales] >='500', ,rgb(255,110,110),rgb(108,179,63)), // 'Then' part for [Product_Group] = '1'

If([Product_Group] = '2', // 'Else' part for [Product_Group] = '1'; Used to create another If(); Condition

If( [sales] >='500', ,rgb(255,110,110),rgb(108,179,63)), // 'Then' part for [Product_Group] = '2'

If([Product_Group] = '3'....

)))))) // the last one should close all the above If's

This is a "direct way, there are more elegant and readable solutions like the one proposed by Rupam... using the same Pick function, if ProductGroup is a numerical value that starts at '1' and is autonumeric from there you can use:

Pick([Product_Group], //If Group=1 it will pick the 1st expression, if group=2 it takes the 2nd..

// Group1

if( [sales] >='500' ,rgb(255,110,110),rgb(108,179,63)),

// Group2

if( [sales] >='500' ,rgb(255,110,110),rgb(108,179,63)),

....

)

Re: Multiple if statements under expression text color

Anonymous — Tue, 22 Nov 2016 14:59:11 GMT

Thank you very much for this idea. I will consider trying this. However, I need to figure out the stuff about the budget. I am working with some pretty new data in many regards, so I am not sure I have this exact possibility. Also it is over many weeks.

But thank you!

Re: Multiple if statements under expression text color

Anonymous — Tue, 22 Nov 2016 15:16:33 GMT

Hey Ruben,

Thanks a lot for the detailed answer!

Yeah I can see that my way of trying to do it does not follow the function syntaxis. However, I thought that it would be possible to use an "and" statement to state that for each if function it had to be both a car model and a specific sales amount that was used.

Unfortunately the product groups are not autonumeric, as they are different car models (7 different ones), and I therefore do not think using Pick will be possible?

As I understand your other answer, then an if statement is used for both the "then" and "else" part? where in the "then" part I determine the condition for the coloring of for example product 1, and in the else part I actually start a new if statement with product 2,3, etc.?

So overall it should determine it if it product 1, and do the coloring, and if not then look if product 2, and so on?

Just trying to make sure I understand it, because then I will try it out and work with this. I can see that the "Pick" should be more clear, but I am not sure it works with my dimension

Re: Multiple if statements under expression text color

rubenmarin — Tue, 22 Nov 2016 15:17:54 GMT

Hi Mathias, if I understand it right to use Julian's idea you only need an inline table (or an external file) that sets the value for each product group, in example:

LOAD * Inline [

[Product_Group], Budget

1, 500

2, 500

...

];

So you can use a lot simpler expression like:

=if(Sum([sales])>= Budget, rgb(255,110,110),rgb(108,179,63))

BTW those colors are using red when sales are higher than budget and green when is sales are lower than the value. Maybe it should be:

=if(Sum([sales])>= Budget, rgb(108,179,63), rgb(255,110,110))

Re: Multiple if statements under expression text color

Anonymous — Tue, 22 Nov 2016 15:24:38 GMT

Yeah okay, I could see that being a possibility also. I will see if I can work out something with this solution also. Thank you!

Re: Multiple if statements under expression text color

rubenmarin — Tue, 22 Nov 2016 15:28:27 GMT

Hi Mathias, yes, you can nest different If's, in the 'Then' part you add what you want to do when conditions are meet, and you use the 'Else' part to continue with other conditions, the 'Else' part of the last 'If' is when none of the above conditions where met.

Note that order is important when nesting if, in example:

If(Sum(Sales)>1000, rgb(...),

If(Sum(Sales)>2000, rgb(...) // It will never execute this 'Then' part, if sum(Sales)>2000 the 'then' part of the first 'If' is executed (because is also >1000) and it doesn't reaches the second 'If'. If Sum(Sales)<=1000 it will not enter the 'Then' part of the first 'If', neither the 'Then' part of the 2nd 'If'

For use Pick with strings you'll need some workaround so we better avoid this until neccesary, so far I see Julian's answer the best option.

Re: Multiple if statements under expression text color

Anonymous — Tue, 22 Nov 2016 15:45:54 GMT

Hey Ruben,

Alright good. I do understand your way of doing it now, and I will try with this. However, as you also write, overtime Julian's answer is most likely the best, as it is a lot less complicated.

Re: Multiple if statements under expression text color

Anonymous — Tue, 22 Nov 2016 15:46:42 GMT

Overall I guess I could work with any of your answers. I will try them all out, and see what works best for me. Thank you all for the very fast and good responses!

Re: Multiple if statements under expression text color

julian_rodriguez — Tue, 22 Nov 2016 15:53:48 GMT

Good luck Mathias,

I'm glad than I could help you.

Regards!