topic Calculation of % change for a given Amount in QlikView

Calculation of % change for a given Amount

a0693051 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Calculation of % change for a given Amount

sunny_talwar — Tue, 17 Jul 2018 11:21:32 GMT

Try this

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

Re: Calculation of % change for a given Amount

a0693051 — Tue, 17 Jul 2018 15:22:31 GMT

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

Re: Calculation of % change for a given Amount

sunny_talwar — Tue, 17 Jul 2018 15:28:25 GMT

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

Re: Calculation of % change for a given Amount

a0693051 — Wed, 18 Jul 2018 08:29:34 GMT

Hi Sunny,

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

Thanks & Regards

Jhanaarthanan

Re: Calculation of % change for a given Amount

a0693051 — Wed, 18 Jul 2018 10:19:55 GMT

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

Re: Calculation of % change for a given Amount

sunny_talwar — Wed, 18 Jul 2018 10:23:53 GMT

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?

Re: Calculation of % change for a given Amount

a0693051 — Wed, 18 Jul 2018 13:34:35 GMT

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

Re: Calculation of % change for a given Amount

sunny_talwar — Wed, 18 Jul 2018 14:44:43 GMT

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

Re: Calculation of % change for a given Amount

a0693051 — Wed, 18 Jul 2018 14:49:50 GMT

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

Re: Calculation of % change for a given Amount

sunny_talwar — Wed, 18 Jul 2018 14:56:21 GMT

There is two ways to resolve this issue

1) Use The As-Of Table for the month to month comparison, or

2) Generating Missing Data In QlikView so that the Months don't go away when you make a selection in Buyer field.

Re: Calculation of % change for a given Amount

a0693051 — Wed, 18 Jul 2018 15:22:02 GMT

I have attached the QVW and please help me to resolve the issue.

Re: Calculation of % change for a given Amount

sunny_talwar — Wed, 18 Jul 2018 15:23:10 GMT

Which method do you want to go with? I suggest using The As-Of Table... if you are okay... I can show you how this can be done

Re: Calculation of % change for a given Amount

a0693051 — Wed, 18 Jul 2018 15:28:35 GMT

Thanks Sunny, Please proceed with The As-Of Table which you suggested

Re: Calculation of % change for a given Amount

sunny_talwar — Wed, 18 Jul 2018 15:35:41 GMT

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

Re: Calculation of % change for a given Amount

a0693051 — Thu, 19 Jul 2018 08:37:02 GMT

Thanks Sunny, we will apply the logic and get back to you.

Re: Calculation of % change for a given Amount

a0693051 — Tue, 31 Jul 2018 08:23:39 GMT

Thanks Sunny, Its working. Thanks a lot!