Calculation of % change for a given Amount

[a0693051]

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]

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.

[a0693051]

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

[sunny_talwar]

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

[a0693051]

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

[sunny_talwar · Accepted solution]

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

[a0693051]

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

[a0693051]

Thanks Sunny, Its working. Thanks a lot!