Solved: Calculation of % change for a given Amount - Qlik Community

a0693051 · ‎2018-07-17

Hi All,

We are trying to create a '% Change' pivot table for the data as attached (Sheet1 = data & Sheet 2 = Sample table).

we are using fields Months, Buyer and Amount_Requested.

We are trying to achieve monthly % change in Amount_Requested for a given buyer using the below formula :

% Change = ((Amount_Requested(current month) - Amount_Requested (previous month)) / Amount_Requested (previous month))*100

we are unable to display the data in Pivot table.

Thanks and Regards

sunny_talwar · ‎2018-07-18

Script

Table:

LOAD Month(Date#(Month, 'MMM')) as Month,

Buyer,

[Amount Requested];

LOAD * INLINE [

Month, Buyer, Amount Requested

Jan, Buyer1, 7000

Jan, Buyer2, 5600

Jan, Buyer3, 8600

Jan, Buyer4, 5600

Feb, Buyer1, 8900

Feb, Buyer2, 7009

Feb, Buyer3, 7980

Mar, Buyer1, 4000

Mar, Buyer2, 6600

Mar, Buyer3, 2300

Apr, Buyer1, 2345

Apr, Buyer2, 5648

Apr, Buyer4, 3694

May, Buyer1, 9687

May, Buyer2, 4532

May, Buyer4, 7890

Jun, Buyer2, 4572

Jun, Buyer3, 7864

Jun, Buyer4, 4321

Jul, Buyer3, 2314

Jul, Buyer4, 8546

Aug, Buyer1, 5467

Aug, Buyer4, 1875

Sep, Buyer1, 6962

Sep, Buyer2, 7890

Oct, Buyer2, 9456

Oct, Buyer3, 5500

Nov, Buyer3, 7384

Nov, Buyer4, 8800

Dec, Buyer4, 9252

Jan, Buyer5, 5000

Apr, Buyer5, 6000

Sep, Buyer5, 80000

];

AsOfTable:

LOAD DISTINCT Month as AsOfMonth,

Month,

'CM' as Flag

Resident Table;

Concatenate (AsOfTable)

LOAD DISTINCT Month as AsOfMonth,

Month - 1 as Month,

'PM' as Flag

Resident Table;

When nothing is selected

When Buyer1 is selected

When Buyer2 is selected

Expression

=Sum({<Flag = {'CM'}>}[Amount Requested])/Sum({<Flag = {'PM'}>}[Amount Requested])-1

View solution in original post

sunny_talwar · ‎2018-07-17

Try this

=Sum([Amount Requested])/Before(Sum([Amount Requested]))-1

a0693051 · ‎2018-07-17

Thanks for your reply Sunny, but it is not working as we expected.

sunny_talwar · ‎2018-07-17

Can you elaborate? Didn't you want 27.14%? What is wrong?

a0693051 · ‎2018-07-18

Hi Sunny,

I am preparing the detailed sample data and what we expect in the output and update you soon.

Thanks & Regards

Jhanaarthanan

a0693051 · ‎2018-07-18

Hi Sunny,

Please refer the attached excel sheet Sample Data New.xlsx for sample input data and what we expect from the output.

Formula for calculating % Change = ((Amount_Requested(current month) - Amount_Requested (previous month)) / Amount_Requested (previous month))*100

Thanks & Regards

Jhanaarthanan

sunny_talwar · ‎2018-07-18

Okay, so I replaced the old data with the new data and performed a reload... what is the issue now?

To me, I can't spot any differences, can you?

a0693051 · ‎2018-07-18

Thanks Sunny, Its working as expected for most of the cases. I have given one scenario where need more advise. This is mainly happening when applying the selections.

Given below the details by Buyer also added ‘Buyer 5’ for more clarity.

1. If you select only particular buyer then 100% values are went off, Ex: June and October month data (100%) is missing for Buyer1.

And new value added for Aug month.

2. If you select Buyer2, Sep month data showing as 72.57%, but it is wrong.

3. For Buyer3, Jun and Oct month data is wrongly displayed.

4. For Buyer4, May and Nov month data is wrong.

5. I have added Buyer5 for below data:

Jan, Buyer5, 5000

Apr, Buyer5, 6000

Sep, Buyer5, 80000

Apr and Sep data is wrongly displayed.

Please advise.

Thanks & Regards

Jhanaarthanan

sunny_talwar · ‎2018-07-18

Where is Buyer 5 added? Did you update one of the existing Excels or intended to add a new one?

a0693051 · ‎2018-07-18

Please add yourself in QVW inline table and see the results.