Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
radupanciuc
Contributor II
Contributor II

Calculate Difference in Rows in Pivot

Hello,

I have a pivot table in Qlik Sense for which I want to see how the measures changed from one year to another.

In the rows I have customer name and the year (2016&2017) and in the measures i have several sums or other calculations. I would like to see the difference between years as well. The table looks something like this:

    

CustomerYearSalesCost
Customer A201610060
201715080
Difference5020

What is the best way to do this?

Thanks,

Radu

1 Reply
OmarBenSalem
Partner
Partner

SUPPOSE U HAVE THIS:

tab:

load * Inline [

Customer, Year, Sales, Cost

CustomerA,  2016, 100,60

CustomerA,  2017, 150,80

CustomerB,  2016, 180,90

CustomerB,  2017, 150,80

];

Create a pivot table:

as dimension: Customer

Add line : Year

as measure1:

if(Dimensionality()=1, 'Difference between: '&max(Year)&' and '&(max(Year)-1)&' for '&Customer &' is ' &(sum({<Year={"$(=max(Year))"}>}Sales)-sum({<Year={"$(=max(Year)-1)"}>}Sales)),

Sum(Sales))


color it by expression :

if(Dimensionality()<>1,White(), if(Dimensionality()=1  and (sum({<Year={"$(=max(Year))"}>}Sales)-sum({<Year={"$(=max(Year)-1)"}>}Sales)) >0,

Green(), red()))



as Measure2:

if(Dimensionality()=1, 'Difference between: '&max(Year)&' and '&(max(Year)-1)&' for '&Customer &' is ' &(sum({<Year={"$(=max(Year))"}>}Cost)-sum({<Year={"$(=max(Year)-1)"}>}Cost)),

Sum(Cost))


Color it by expression :

if(Dimensionality()<>1,White(), if(Dimensionality()=1  and (sum({<Year={"$(=max(Year))"}>}Cost)-sum({<Year={"$(=max(Year)-1)"}>}Cost)) >0,

Green(), red()))


result:

Capture.PNG