Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone. I need help with 2 excels I'm working on. I started to use the app no more than a month ago, the syntaxis guide and this forum has been really useful but I'm here, stuck again. Found it really hard to make a coherent topic.
Please, follow the information and you will understand what I want.
I have these, 2 excels with the following info:
client table
customer | client | sub# |
coca | 4 | 1 |
pepsi | 3 | 2 |
sprite | 5 | 3 |
fanta | 6 | 4 |
mirinda | 7 | 5 |
water | 8 | 6 |
AND
Sales table:
client name | client | sales Q |
coca | 4 | 44 |
pepsi | 3 | 30 |
sprite | 5 | 493 |
fanta | 6 | 2 |
mirinda | 7 | 2 |
water | 8 | 13 |
coca | 4 | 34 |
pepsi | 3 | 413 |
sprite | 5 | 405 |
fanta | 6 | 40 |
mirinda | 7 | 300 |
water | 8 | 1050 |
coca | 4 | 100500 |
pepsi | 3 | 94 |
The client name in the Sales table is pretty much endless because it does not sum the "sales Q" column by client.
What I want to show in my table in Qlik Sense is the following:
client name | client | sales Q |
coca | 4 | 5444 |
pepsi | 3 | 4430 |
sprite | 5 | 2493 |
fanta | 6 | 442 |
mirinda | 7 | 13232 |
water | 8 | 413 |
I tried a simple thing which was sum("sales Q") but it says Invalid Dimension. Maybe I should not be using the table or I'm doing it wrong....
Now. something else, I managed to paint the "sales Q" cells in Qlik Sense.
if(sum("sales Q")> 3000, 'green()', blue())
But I want to paint them by client, example:
If pepsi or coca is > 3000, red, blue
if sprite, fanta, mirinda, water are > 250, green, red.
How can I write this in the formula?
Thank you in advance.
Regards.
Is this you want?
Color expression goes here:
Color expression:
If(Match([client name], 'coca', 'pepsi'),
If(Sum([sales Q])> 3000, Red(), Blue()),
If(Sum([sales Q]) > 250, Green(), Red()))
To answer your first question, this should be easy:
1) Create a chart with dimension "client name"
1b) If needed, create a second dimension "client"
2) Create an expression
=Sum("sales Q")
I said I did that sum but it gives me Invalid Dimension.
Is this you want?
Color expression goes here:
Color expression:
If(Match([client name], 'coca', 'pepsi'),
If(Sum([sales Q])> 3000, Red(), Blue()),
If(Sum([sales Q]) > 250, Green(), Red()))
You just need to select the appropriate field name from the drop down list.
Where does it give you an error, could you post your app or screenshots?
Hi Patricio,
You can even make use of the ColorMix() function if you have more complex calculations while coloring.
Thanks,
Sangram Reddy.
Thank you Sunny-T