Hi Team,
I need help. Please find below the sample.
C Category | Category | 2018-01 | 2018-02 | 2018-03 |
A | AC | 322,165 | 304,638 | 299,908 |
B | AC | 461,108 | 409,994 | 779,603 |
C | AC | 1,449,944 | 1,751,532 | 1,763,404 |
D | AC | 21,215 | 23,787 | 26,406 |
A | BC | 310,499 | 310,499 | 316,931 |
B | BC | 426,705 | 426,705 | 426,705 |
C | BC | 1,362,659 | 1,362,659 | 1,362,659 |
D | BC | 24,755 | 24,755 | 24,755
|
the output will be- The calcuation is (AC/BC) for each C category.
OUTPUT | |||
C Category | 2018-01 | 2018-02 | 2018-03 |
A | 1.037573 | 0.981124 | 0.946288 |
B | 1.080625 | 0.960837 | 1.827029 |
C | 1.064055 | 1.285378 | 1.294091 |
D | 0.85701 | 0.960905 | 1.066685 |
thanks for the help in advance
Solved! Go to Solution.
Hi @sriniprad08 ,
Here is a workflow that achieves this outcome:
Let me know if you have any questions
Thank you so much @jamielaird . Really helpful.
Hi @jamielaird ,
Thank you for all the support. I need help. When i tried to run with the larger data, i am getting below warning and it's not calculating correctly. Please let me know your thoughts,
Thanks ,
Sri
Hi @sriniprad08
The numbers that are generating error messages are differently structured from the examples you originally posted. Something might have gone wrong further upstream in your workflow or in your source data.
Can you provide some examples of the "Actual" values in the larger dataset that are causing you problems?
Hi @jamielaird ,
Thank you for the reply. Yes i think you are right . data has changed. Please find attached the sample
Region | FC | 2012-01 | 2012-02 |
ES | AC | $ 134,343.04 | $ 1,175,400.32 |
ES | AC | $ 33,434,324.03 | $ 10,255,362.21 |
ES | AC | $ 69,834,343.43 | $ 938,156.36 |
ES | AC | $ 6,138,121.98 | $ 8,367,451.48 |
ES | AC | $ 1,141,218.62 | $ 734,581.36 |
ES | AC | $ 3,665,372.91 | $ 5,271,751.13 |
ES | AC | $ 1,111,895.48 | $ 1,410,534.47 |
ES | AC | $ 16,754,975.47 | $ 21,601,329.04 |
ES | BC | $ 1,484,531.69 | -$ 283,764.02 |
ES | BC | $ 12,029,195.97 | -$ 1,846,354.54 |
ES | BC | $ 1,714,693.56 | -$ 1,111,740.28 |
ES | BC | $ 7,446,809.78 | -$ 2,323,129.63 |
ES | BC | $ 1,210,998.10 | -$ 2,363,167.04 |
ES | BC | $ 4,240,395.68 | -$ 3,266,488.35 |
ES | BC | $ 1,350,858.93 | -$ 403,628.26 |
ES | BC | $ 18,786,938.38 | -$ 2,738,109.35 |
Please let me know your thoughts,
Thanks and Regards,
Sri
Hi @sriniprad08 ,
Thanks for sharing a second set of sample data. As you have noted, the structure of the data is different from your first set of sample data, which means the original solution won't work.
Using BOTH the first and second sets of sample data I have developed a more dynamic solution. There are some basic requirements for the data that you feed into this:
The basic steps within the workflow are:
As a next step I would recommend you test the solution against a wider set of input data. If your data varies significantly, you will always have to go through a process of standardizing it before you can expect a pre-built set of workflow steps to give you the desired output.
This would be a great use case for a macro, but I don't want to overcomplicate things by going into that now.
Hope this was helpful!
Hi @jamielaird ,
Thank you so much. Really appreciate all the support. I will definitely try with the different set.
Also if you can share your thoughts on the Macro that would be very helpful to work on for future.
Cheers,
Sr
Hi @jamielaird ,
You have included multi row formula. Can you explain the reason for this? I would like to learn.
cheers,
Sr
Hey @sriniprad08 ,
I've attached an annotated image explaining how the Multi-Row Formula tool is used in this instance to number each group of records:
To learn more, I suggest checking out the Tool Mastery blog on the Multi-Row Formula: