6 Replies Latest reply: May 2, 2017 9:37 AM by Celia Seward

# Calculating change percentage between PriorYear Sales and Current Year Sales

This may be a complex question to put out here, but I haven't been able to attend the Data_Modeling for Qlik Sense class yet.  It keeps getting canceled due to low enrollment.

So, I want to calculate the change percentage of sales from year to year in a Pivot table (or a regular table, but prefer pivot table).

• ###### Re: Calculating change percentage between PriorYear Sales and Current Year Sales

I should add that I use Qlik Sense.

• ###### Re: Calculating change percentage between PriorYear Sales and Current Year Sales

Any sample data with requirement would be great to understand your issue

• ###### Re: Calculating change percentage between PriorYear Sales and Current Year Sales

Is this fine what your are looking for

• ###### Re: Calculating change percentage between PriorYear Sales and Current Year Sales

I think I could use this format - yes.

• ###### Re: Calculating change percentage between PriorYear Sales and Current Year Sales

I've attached a spreadsheet that contains all the data.  Look at the "Billed" worksheet(table), I hope the attachment comes through.  This data contains 4 school years.

I want a pivot table like below using the "Billed" data.

 Total Cost by Discipline 2015-2016 2016-2017 Change % Occupational Therapy \$96,488 \$108,280 12% Physical Therapy \$26,564 \$31,298 18% Psychological \$27,540 \$3,231 -88% Psychological - Bi-Lingual \$0 \$6,000 na Speech Therapy \$285,163 \$352,204 24% Total \$435,755 \$501,013 15%
• ###### Re: Calculating change percentage between PriorYear Sales and Current Year Sales

Hi,

i see 2 options :

- Design option : The above function in the qlik object - above(sum(Sales)) will use the value as in previous cell

- Modeling option : Better for data discovery : create a asofcalendar table to your model and link for example :

Year / AsOfYear / AsOfType

2016 / 2016 / Direct

2015 / 2016 / N-1

Then use this AsOfYear dimension in object and you measure would be : (Sum({<AsOfType={'Direct'}>}Sales)/Sum(<AsOfType={'N-1'}>}Sales))-1