Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following table:
Row No | RMA No | Sequence No | From Status | To Status |
1 | BRH1N10021 | 1 | Keying | Repair |
2 | BRH1N10021 | 2 | Repair | Ship |
3 | BRH1N10021 | 3 | Ship | Closed |
4 | BRH1N20034 | 1 | Keying | Repair |
5 | BRH1N20034 | 2 | Repair | Ship |
6 | BRH1N40012 | 1 | Keying | Repair |
7 | BRH1N40025 | 1 | Keying | Repair |
My desired result is to alternate between background colors when the "RMA No" value changes. Example (consider the font color as the background color):
Row No | RMA No | Sequence No | From Status | To Status |
1 | BRH1N10021 | 1 | Keying | Repair |
2 | BRH1N10021 | 2 | Repair | Ship |
3 | BRH1N10021 | 3 | Ship | Closed |
4 | BRH1N20034 | 1 | Keying | Repair |
5 | BRH1N20034 | 2 | Repair | Ship |
6 | BRH1N40012 | 1 | Keying | Repair |
7 | BRH1N40025 | 1 | Keying | Repair |
The code that you want for the background colour on each of your cells is:
if(mod(match([RMA No], '$(=concat(distinct [RMA No], chr(39) & ',' & chr(39)))'),2) = 0, rgb(230,230,255), rgb(240,240,240))
This will give you something like this:
I've attached the app that I tested this with, should you need it.
Now, for the reason why it works!
The CONCAT statement will give you a list of all values grouped together. We use $(= ) to calculate this outside of the chart, to consider all dimension values. The DISTINCT statement means each one appears only once and the parameter with chr(39) & ',' & chr(39) comma separates the values, and the opening and closing quotes are outside the dollar expansion. This gives us a comma separated list, which we can do a match of the current dimension value, and that will return the RMA's position in the list. Doing a mod 2 of that will return either 0 or 1 - that is then used in an IF statement to get the colour.
It relies on the order of the CONCAT values and the order of the table being the same, but if you are sorting by RMA then that should be the case.
Hope that this gives you what you need!
Cheers,
Steve
use below expression in "Text Color Expression" configuration of your table chart:
if (floor(random) >0 and floor(random)<10 , RGB(204,229,255),
if (floor(random) >10 and floor(random)<20 , RGB(102,204,0),
if (floor(random) >20 and floor(random)<30 , RGB(233,50,0),
if (floor(random) >40 and floor(random)<50 , RGB(255,0,120)))))
The code that you want for the background colour on each of your cells is:
if(mod(match([RMA No], '$(=concat(distinct [RMA No], chr(39) & ',' & chr(39)))'),2) = 0, rgb(230,230,255), rgb(240,240,240))
This will give you something like this:
I've attached the app that I tested this with, should you need it.
Now, for the reason why it works!
The CONCAT statement will give you a list of all values grouped together. We use $(= ) to calculate this outside of the chart, to consider all dimension values. The DISTINCT statement means each one appears only once and the parameter with chr(39) & ',' & chr(39) comma separates the values, and the opening and closing quotes are outside the dollar expansion. This gives us a comma separated list, which we can do a match of the current dimension value, and that will return the RMA's position in the list. Doing a mod 2 of that will return either 0 or 1 - that is then used in an IF statement to get the colour.
It relies on the order of the CONCAT values and the order of the table being the same, but if you are sorting by RMA then that should be the case.
Hope that this gives you what you need!
Cheers,
Steve
Thank you! This works but I must point out that only if you order the table by RMA No and/or RowNo
Yes, I was aware that the sort order needs to be fixed in order for it to work properly. Sadly there is no option to fix the sort order. It was an option in QlikView (Sense's older sibling), so it's a real shame that it hasn't carried through into Sense. Qlik's desire to make things end-user configurable removes some required control from application designers. The fact you can't lock pivot tables tends to be even more of a pain, in my opinion.
Thanks for marking my solution as correct, as this helps other Community users.
Cheers,
Steve