Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

alextomlins
New Contributor II

Pivot Table - Weekly difference with Colours

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?

Rev Heatmap example.PNG

1 Solution

Accepted Solutions
brunobertels
Valued Contributor

Re: Pivot Table - Weekly difference with Colours

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

6 Replies
YoussefBelloum
Esteemed Contributor

Re: Pivot Table - Weekly difference with Colours

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
New Contributor II

Re: Pivot Table - Weekly difference with Colours

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 !Smiley Happy

brunobertels
Valued Contributor

Re: Pivot Table - Weekly difference with Colours

Capture.PNGHi 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
New Contributor II

Re: Pivot Table - Weekly difference with Colours

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
Valued Contributor

Re: Pivot Table - Weekly difference with Colours

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
New Contributor II

Re: Pivot Table - Weekly difference with Colours

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

Thanks,

Alex

Community Browser