Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am trying to achieve a dynamic comparison between fields in a pivot table (quarter on quarter in this specific case).
I have tried to build a set analysis but, it did not work.
Please note that quarters (Q1, Q2, Q3 and Q4) are set with a mapping reading the date.
Below is my data with no calculated expression.
And following image should be the final result where the trend between current and previous quarter is shown.
Thank you for your help.
QVMM
Hello Vicky,
Thank you very much for the script.
It looks fantastic but unfortunately when adapted to my data (file not inline) it overstates the overall value and percentages (e.g. 518.709.020 instead of 269.599 for business 01).
Any reason why?
Thank you and kind regards,
QVMM
Below the two pivot images not attached previously.
Lines of Business | Year | Quarter | Q2 | Q3 |
Business 01 | 2011 | 269599 | 297181 | |
Business 02 | 2011 | 553323 | 560626 | |
Business 03 | 2011 | 1099036 | 1132336 | |
Business 04 | 2011 | 125084 | 129675 | |
Business 05 | 2011 | 1473839 | 1505941 | |
Business 06 | 2011 | 1103914 | 1129369 | |
Business 07 | 2011 | 666454 | 673922 | |
Business 08 | 2011 | 1985235 | 2031452 | |
Total | 7276484 | 7460502 |
Lines of Business | Year | Q2 | Q3 | Q3 vs Q2 |
Business 01 | 2011 | 269599 | 297181 | 110.2% |
Business 02 | 2011 | 553323 | 560626 | 101.3% |
Business 03 | 2011 | 1099036 | 1132336 | 103.0% |
Business 04 | 2011 | 125084 | 129675 | 103.7% |
Business 05 | 2011 | 1473839 | 1505941 | 102.2% |
Business 06 | 2011 | 1103914 | 1129369 | 102.3% |
Business 07 | 2011 | 666454 | 673922 | 101.1% |
Business 08 | 2011 | 1985235 | 2031452 | 102.3% |
7276484 | 7460502 | 102.5% |
Regards,
QVMM
Hi qvmm,
It's without SetAnalysis. I have solve this with a calculation in script. See attached file.
Regards vicky
Hello Vicky,
Thank you very much for the script.
It looks fantastic but unfortunately when adapted to my data (file not inline) it overstates the overall value and percentages (e.g. 518.709.020 instead of 269.599 for business 01).
Any reason why?
Thank you and kind regards,
QVMM
Hi,
Pleae upload sample application for reference.
Maybe you can use these solutions:
=sum(amount) / before( sum(amount) )
or
=column(ColumnNo()) / before( column(ColumnNo()) )
Regards