Heatmap: Kleur per dimensie item

[R3lph]

Hallo,

Ik heb een draaitabel gemaakt met 2 dimensies en 1 expressie, door kleuren eraan toe te voegen ontstaat een heatmap.

De formule geldt voor alle dimensieregels en graag zou ik zien dat de formule geldt voor elke dimensie item afzonderlijk. 

De dimensie items staan los van elkaar en hebben geen relatie, de waardes lopen te veel uit elkaar om te vangen in een formule

Graag zou ik het gemiddelde van alle waardes per dimensie item willen uitrekenen en de individuele waardes die bijvoorbeeld 10% hoger of lager liggen een andere kleur willen geven. Dus binnen de bandbreedte groen en buiten de bandbreedte van het gemiddelde de kleur oranje. De kleur kan ik regelen via de background color in het expressie menu. Alleen geldt wordt dan het gemiddelde over alle waardes van alle dimensie items genomen. En dit is niet wat ik wil.

Bijgevoegd het voorbeeld hoe ik het nu heb maar wat niet correct is omdat de kleuring gaat over het gemiddelde van alles.

[SBN]

Hi there R3lph,

I'd like to help you out with your question.
From the screenshot it looks like you're using Qlik View?
I'm only familiar with QliK Sense.
Would you be able to add a small example QVD with dummy data and some additional information?
So I can fiddle with that?

Thanks,

SBN

[R3lph]

Beste SBN,

Bedankt voor reactie.

Ik heb inderdaad alleen Qlikview en geen Qliksense.

Bijgevoegd de QVW en niet de QVD helaas.

Hopelijk kun je hier wel iets mee, de formule is naar mijn mening gelijk voor beide applicaties.

Gr,

Ralph

[SBN]

Hi there R3lph,

It took some time to make some time to install Qlik Sense Desktop, verify the installation and convert the QVW to a Qlik Sense App.
I managed to do that for the first time...😉

It's a bit of a strange conversion because the dimensions, measurements and visualizations all got converted to master items, but ok.
Also the pivot table didn't convert as expected.
I expected the "Melddatum_sortering" to be converted as a column, but got converted to a dimension.

Unfortunately the coloring expression for the measurement "Aanvragen" isn't converted.

Would you be so kind as to mention the coloring expression please?

Kind regards,

SBN

[R3lph]

Hallo SBN,

Goed bezig! 👍

De kleurstelling geldt alleen voor per item (Item A, Item B, etc) en niet over de gehele tabel.

Ik zou graag het gemiddelde van alle getallen in Item A willen afzetten ten opzicht van de waarde in de cel.

Als de waarde boven het gemiddelde is oranje en is hij onder het gemiddelde dan groen.

voorbeeld:

Item A: 2 4 6 2 4 10 5 9 8   -> het gemiddelde is 5,55. Alle waarde gelijk aan en boven 5,55 oranje. Alles onder de 5,55 groen.

Item B: 22, 26,30,48,50,34,38,40 -> gemiddelde is 36 Alle waarde gelijk aan en boven 36 oranje. Alles onder de 36 groen. 

De formule is niet het probleem, maar de scheiding tussen de items krijg ik niet voor elkaar.

Dank voor je tijd. 

[SBN · Accepted solution]

Hi @R3lph,

I found the right expression.
Ok, just to make it a bit more educational for anyone else who'll come by this thread.

So basically what you're looking for is an expression with which you can color the pivot table cells.
The expression should evaluate if the shown count of "Requests" for the "Item" dimension in the "Period" column is greater or equal to the average count of "Requests" for the "Item" dimension based on all "Periods" where there have been "Requests" for this "Item".

I started of by calculating the following values:

1. Total count of "Requests" per "Item":
   =Count({$<Melding_soort={'Storing'}>} Distinct Meldingnr)
   
   
2. Total count of "Periods" with "Requests" per "Item":
   =Count({$<Melding_soort={'Storing'}>} Distinct Melddatum_sortering)
   
   
3. The average count of "Requests" per "Item":
   =Count({$<Melding_soort={'Storing'}>} Distinct Meldingnr)
   / Count({$<Melding_soort={'Storing'}>} Distinct Melddatum_sortering)

I ended up with the following pivot table:

Now that we know the basic expression to calculate the average count of "Requests" per "Item" we can take this expression to see if we can determine if the count of "Requests" in the "Period" is above or below this average.

I created a new pivot table so I could try to extend the "average" expression, being calculation number 3, to show me "Yes" when it is greater or equal to the average count of "Requests" or "No" when it isn't.

It took me some fiddling, but this is the expression I ended up with:

If(

Count({$<Melding_soort ={'Storing'}>} Distinct Meldingnr) 

>= 

    Aggr(NoDistinct Count({$<Melding_soort={'Storing'}>} Distinct Meldingnr), Ruimteboek_groep_Anomiem) 

    /

    Aggr(Nodistinct Count({$<Melding_soort={'Storing'}>} Distinct Melddatum_sortering), Ruimteboek_groep_Anomiem)

, 'Yes'

  , 'No'

 )

And that created the following pivot table:

If we dissect the expression than the first bold part is the original count expression that counts the "Requests" per period:

If(

Count({$<Melding_soort ={'Storing'}>} Distinct Meldingnr) 

>= 

    Aggr(NoDistinct Count({$<Melding_soort={'Storing'}>} Distinct Meldingnr), Ruimteboek_groep_Anomiem) 

    /

    Aggr(Nodistinct Count({$<Melding_soort={'Storing'}>} Distinct Melddatum_sortering), Ruimteboek_groep_Anomiem)

, 'Yes'

  , 'No'

 )

The next bold part of the expression is the "average" count expression that I created in the first pivot table:

If(

Count({$<Melding_soort ={'Storing'}>} Distinct Meldingnr) 

>= 

    Aggr(NoDistinct Count({$<Melding_soort={'Storing'}>} Distinct Meldingnr), Ruimteboek_groep_Anomiem) 

    /

    Aggr(Nodistinct Count({$<Melding_soort={'Storing'}>} Distinct Melddatum_sortering), Ruimteboek_groep_Anomiem)

, 'Yes'

  , 'No'

 )

We need to "escape" from the confinement of the "Period" column to calculate the average of "Requests" for the "Item".
So we need to aggregate the "Item" dimension with a "Nodistinct":

If(

Count({$<Melding_soort ={'Storing'}>} Distinct Meldingnr) 

>= 

    Aggr(NoDistinct Count({$<Melding_soort={'Storing'}>} Distinct Meldingnr), Ruimteboek_groep_Anomiem) 

    /

    Aggr(Nodistinct Count({$<Melding_soort={'Storing'}>} Distinct Melddatum_sortering), Ruimteboek_groep_Anomiem)

, 'Yes'

  , 'No'

 )

Now if we translate this expression to a color expression for the final pivot table this would look like this:

If(

Count({$<Melding_soort ={'Storing'}>} Distinct Meldingnr) 

    >= 

    Aggr(NoDistinct Count({$<Melding_soort={'Storing'}>} Distinct Meldingnr), Ruimteboek_groep_Anomiem) 

    /

    Aggr(Nodistinct Count({$<Melding_soort={'Storing'}>} Distinct Melddatum_sortering), Ruimteboek_groep_Anomiem)

, rgb(235, 192, 52)

, rgb(113, 235, 52)

 )

And that will color your pivot table like this:

I found this question rather educational to solve...😉

Hope this will help you out.

Kind regards,

SBN

Btw I attached the QVF with all my experiments...😉

[R3lph]

Hello SBN,

Thank you very much for the solution and the way you describe it.

I'm glad you learn also something about it.

Now I gonna read the answer so I understand it and download the attachment to get it in Qlikview.

Kind regards,

Ralph