Pivot Table - Weekly difference with Colours

[alextomlins]

Hi All,

I'm trying to do something i can do in excel but on a pivot table in QlikSense.

What i would like to do is to look at revenue actual financial difference week on week on a table. So rows would be product, Column YearWeek and revenue difference between that week and the previous week. Then i would like to colour the difference in a heatmap style? Would i need to do this in the script or can i use a formula in the UI?

[YoussefBelloum]

Hi,

it would be a bit complicated to calculate and set colors on the script, it is better to use the dedicated coloring options present on the front-end

what is your coloring calculation rules? when you want to color? when it is red?green ? orange ?

it is better to attach a portion of the data present on the Image abover than attaching an image, to be able to make some tests

[alextomlins]

It was going to be a sweeping conditional colours however colours is not as important as the difference in the pivot table. Unfortunately i cannot really share the data

The colours would be out of a range of that weekyear, So any big winners that week would be green and loser would be red and the in between would be shades in between

I would be happy if i succeeded in just showing the difference week on week per product - Do i have to do that in a script?

Thanks for your help !:)

[brunobertels]

Hi Alexander

To achieve your requierment

built your pivot table into Qlik sense

dimension in rows Row Label

dimension in column Week

Then put your mesure

In the mesure panel in the color by expression try this :

with 2 colors :

ColorMix1( hrank( total column(1))/  NoOfColumns(TOTAL),LightGreen(), lightred())

with 3 colors :

ColorMix2( (hrank(total column(1))/(NoOfColumns(TOTAL)/2))-1, Lightred(),lightgreen(),LightGray())

You may have somethink like this for 2 colors :

[alextomlins]

Hi Bruno - This is fantastic for the colouring!

I am also struggling with the measure. how do i calculate this week revenue minus last week revenue for every product and week.

Is this something i can do in an expression or do i need to build a script?

Thanks

Alex

[brunobertels · Accepted solution]

Hi

You can try may be this

Put as expression

MyMesure - before(MyMesure)

somethink like

sum(revenue) - before(sum(revenue))

assuming that MyMesure is your mesure to calculate the revenue

[alextomlins]

Hero thankyou! This is a great function. I will try the colour expressions now !

Thanks,

Alex