topic Re: Color a Pivot Table in App Development

Color a Pivot Table

antoniodneto — Fri, 15 Mar 2019 15:07:43 GMT

Hello Guys.

I need your help.

I have one pivot table where DATA_BASE is my Dimension, and some rows.

Each row has your specific expression to calc.

I'm trying to find a way to color the values in the row, where highers in red,medium yellow and lowers in green. Making a gradient among them.

My app is attached to understand my view.

Thank you so much guys!!!

Re: Color a Pivot Table

jonvitale — Tue, 17 Apr 2018 14:59:28 GMT

A couple notes:

1. This is a continuation of a discussion from over here in the extensions streams. I suggested moving it over because it probably can be solved with traditional pivot tables.

https://community.qlik.com/message/1472624

2. Antonio really want to color based upon the range in the column. So, a low value in a column might get a red color, a medium value might get yellow, and a high value might get green. However, this is a challenge because it's the rows that are measures in this case. It's easier to base the color of a cell on its range within the measure. Not sure how this can be done across different measures.

3. One more complication. Antonio is using the "before" function to look up the value in the prior column. This seems to make it hard to calculate a max and min to base a color range upon. Here's an example of one of his measures (representing a row).

(SUM(RISCO_C_11_14)+SUM(RISCO_D_15_30)) / before(sum({1}RISCO_A_EM_DIA))

Re: Color a Pivot Table

jonvitale — Wed, 18 Apr 2018 13:16:09 GMT

Antonio,

I had a little time to work on this. I came up with a solution that I think should work for you. It is not very elegant, but it works. Here's what the table looks like (part of it is cut off).

Notice that the color ranges over the columns. So, the lowest value in a column will be red, while the highest will be green. Here's how I did it:

1. Create a variable for each of your measures in the table. Each one of these variables, M1 through M14 is an exact copy-and-paste of the measure you wrote in the table.

2. Create vM1_M12_Max and vM1_M12_Min, which are the max and min value for each of the 12 measures. You can see how they are defined in the image above. Note: If you want to include or remove a measure, you'll have to update these variable definitions.

3. Write a background color expression for each measure that finds the relative value of the measure (on the given day) compared to the min and max of all other measures. To get the red-yellow-green color scale I had to split the ColorMix1 expression into two haves: red-to-yellow for low (< 0.5) and yellow-to-green for high (>= 0.5). The colors are defined in variables so they can be easily adjusted. Here's the code for the first measure, all other measures only replace the vM1 part:

If ( ($(vM1) - $(vM1_M12_Min)) / ($(vM1_M12_Max) - $(vM1_M12_Min)) < 0.5,
ColorMix1(
($(vM1) - $(vM1_M12_Min)) / ($(vM1_M12_Max) - $(vM1_M12_Min)) * 2
        , $(vColor_Low), $(vColor_Mid)
    ),
    ColorMix1(
(($(vM1) - $(vM1_M12_Min)) / ($(vM1_M12_Max) - $(vM1_M12_Min)) - 0.5) * 2
        , $(vColor_Mid), $(vColor_High)
    )
)

Hopefully, this is everything you need. It took some work. Good luck.

Re: Color a Pivot Table

antoniodneto — Wed, 18 Apr 2018 13:33:14 GMT

Hi Jonathan!!!

Man, you worked hard here, thank you so much for your help, I understand everything you created.

One question, I see the gradient between highers and lowers is for the columns but the correct is for the row.

It's complicated to apply by rows not columns?

Thanks again!!!

Re: Color a Pivot Table

jonvitale — Wed, 18 Apr 2018 14:06:04 GMT

Oh man, I really thought that what I did was what you were asking for. It's unfortunate that we didn't understand each other - that was a lot of work.

I don't think you could take the same approach for the rows to create a min and max value for a color range. It's too complicated for me to explain, but the "before" function would make it very difficult, if not impossible.

My one suggestion is somehow to change the data model with the data load editor so that you somehow "move up" the data that is needed at a given time point to the current time point. So instead of calling before(..) you'd have a field with the same information like "previous_data". If you have that it will be much easier to find the min/max.

If you get that, let me know and I'll help.

Re: Color a Pivot Table

antoniodneto — Wed, 18 Apr 2018 14:32:23 GMT

Hi Jonathan! I'm trying to find a solution to not use before.

Can you think Can I use something :

sum({$<DATA_BASE={"$(=DATE(MAX(DATA_BASE,2)))"} >} RISCO_A_EM_DIA))

Actually I tried with no sucess.

Tks

Re: Color a Pivot Table

antoniodneto — Fri, 15 Mar 2019 15:08:47 GMT

Hi Jonathan!

I found one way to remove the BEFORE, I created a new field that brings me what I want.

Follow the app attached.

Do you think is better to solve?

Thanks a lot!!!

Re: Color a Pivot Table

jonvitale — Thu, 19 Apr 2018 01:12:35 GMT

Great,

Can you update the version that I uploaded, which has the measures as variables? It will be a lot easier to work with.

Jonathan

Re: Color a Pivot Table

antoniodneto — Fri, 15 Mar 2019 15:09:53 GMT

Hi Jonathan,

Here it go!!!

Only the ninth row and last 2 I couldn't find a way to remove before.

Maybe to color them I can force the values and colors.

Thank you so much!!

Re: Color a Pivot Table

kaanerisen — Thu, 19 Apr 2018 13:01:55 GMT

Hi Antonio,

If you want to colored the values every row separately. You can use the expression below for all measures on pivot table.

ColorMix2( (hrank(total

{{Measure Expression}}

)/(NoOfColumns(TOTAL)/2))-1 ,red(), green(),Yellow())

With that expression, every row color value calculated individually. Highest value is red, lowest value is green and the other values in the row is gradient over yellow.

Re: Color a Pivot Table

jonvitale — Thu, 19 Apr 2018 13:22:25 GMT

Okay, so here's what it looks like:

I updated the variables to reflect the new measures without the "before" function. I couldn't do so with rows 9, 13, or 14, so they don't have a color.

Here's what the formula looks like in each:

If ( ($(vM1) - Min(total Aggr($(vM1), DATA_BASE))) / (Max(total Aggr($(vM1), DATA_BASE)) - Min(total Aggr($(vM1), DATA_BASE))) < 0.5, 
ColorMix1(
($(vM1) - Min(total Aggr($(vM1), DATA_BASE))) / (Max(total Aggr($(vM1), DATA_BASE)) - Min(total Aggr($(vM1), DATA_BASE))) * 2
        , $(vColor_Low), $(vColor_Mid)
    ),
    ColorMix1(
(($(vM1) - Min(total Aggr($(vM1), DATA_BASE))) / (Max(total Aggr($(vM1), DATA_BASE)) - Min(total Aggr($(vM1), DATA_BASE))) - 0.5) * 2
        , $(vColor_Mid), $(vColor_High)
    )
)

The interesting part here is the Aggr expressions. Basically, what I am doing is asking Qlik to apply my measure $(vM1) to each value of the DATA_BASE dimension. Then I take either the Min or the Max of these values. I need the keyword "total" in the Min or Max function to ensure that the AGGR function will work on all values of DATA_BASE and not just the one we are in at this cell value.

Let me know if you have any questions about how this works. Hopefully, this will solve the problem.

Re: Color a Pivot Table

jonvitale — Thu, 19 Apr 2018 14:04:23 GMT

Ah, that's good stuff. Wish you'd seen this four days ago.

Re: Color a Pivot Table

antoniodneto — Thu, 19 Apr 2018 14:09:22 GMT

Kaan,

Great!!! Is exactly what I am looking for.

One question?

I couldn't apply in the rows "EFICIENCIA 151-180/11-30" "YoY CARTEIRA TOTAL" and "YoY CARTEIRA COMERCIAL"

Can you help me to solve them?

Re: Color a Pivot Table

antoniodneto — Thu, 19 Apr 2018 14:32:02 GMT

Hey Kaan!! I GOT!!!!

Thank you so much!!

Jonathan,

Thank you so much for you time, work and patience. Sorry for anything.

You guys are great!!!

Re: Color a Pivot Table

colinodonnel — Fri, 01 Feb 2019 16:07:21 GMT

That formula is brilliant.

ColorMix2( (hrank(total {{Measure Expression}} )/(NoOfColumns(TOTAL)/2))-1 ,red(), green(),Yellow())

To apply it per column (min/max each column), simply replace NoOfColumns with NoOfRows