Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Try this
=Sum([Amount Requested])/Before(Sum([Amount Requested]))-1
Thanks for your reply Sunny, but it is not working as we expected.
Can you elaborate? Didn't you want 27.14%? What is wrong?
Hi Sunny,
I am preparing the detailed sample data and what we expect in the output and update you soon.
Thanks & Regards
Jhanaarthanan
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
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?
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
Where is Buyer 5 added? Did you update one of the existing Excels or intended to add a new one?
Please add yourself in QVW inline table and see the results.