Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
alextomlins
Contributor III
Contributor III

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
Master
Master

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

View solution in original post

6 Replies
YoussefBelloum
Champion
Champion

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
Contributor III
Contributor III
Author

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
Master
Master

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
Contributor III
Contributor III
Author

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
Master
Master

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
Contributor III
Contributor III
Author

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

Thanks,

Alex