Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want to show in a pivot table evolution of sales (%) by year, month, week and day.
Evolution on year is for example : 2018 vs 2017
Evolution on month is : March 2019 vs March 2018
Week 21 2019 vs Week 21 2018
....
And for day, 2 possibilities : 27/03/2019 vs 27/03/2018 or 27/03/2019 vs 28/03/2018.
Example for years and months in xls :
Can we do a pivot table like this in QlikSense?
Thank you guys !!
Hi,
I suppose you have a table with 3 columns (or more) that contains columns Year, Month and a measure, here is one solution to do it:
Chart: pivot table
Dimensions: Year and Month (like your excel)
Expression:
( ( Sum(Measure)/aggr(Above(total sum(Measure), 12),Year,Month) ) - 1 )
Format the expression as percentage (using the format options in the chart properties) or multiply it by 100
and the qvf attached
Hi Youssef,
Thank you for your help !
It is almost that I want to do !
Like your example, we see the evolution per month but not per year..
Ideally, in the same chart, i want to show evolution per year, per month, and if I can per week.
I find a solution in two columns, one for evolution per month, one for evolution per year but it's not perfect.
Thank you again for your help !
So try this (replace the previous expression above) for the evolution per Years and Months
=if(RowNo()=0,
avg(aggr( ( ( Sum(Sales)/aggr(Above(total sum(Sales), 12),Year,Month) ) - 1 ), Year,Month)),
( ( Sum(Sales)/aggr(Above(total sum(Sales), 12),Year,Month) ) - 1 )
)
Hello Youssef,
Thank you for your help !
I use your set analysis but I have one problem.
I want in one table show Years/Months/Weeks and days. I don't know if it is possible..
For the moment, I can do that by two table (one for Years/Months and other one for Weeks and days).
Ideally, if I can do that in one table it will be perfect.
You can see my two tables.
Thank you again !