The simplest/easiest approach would look something like this:
SUM(IF [Cycle]=2 THEN [Unit] END
SUM(IF [Cycle]=1 THEN [Unit] END
This would require that you have [Item] in your sheet to view the correct results by entity instead of in aggregate.
Just let me know if you were looking for something else.
If this post assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
Thanks for your quick reply,
I forgot to include some additional information in the original questions, my apologies.
I only want to see one cycle at a time in the actual end result, so whilst I am comparing the values of Cycle 1 against Cycle 2 I only want to see the Cycle 2 items in the data, so in my example above, the 5 entities and units all relate to cycle 2. At the moment I've been filtering the cycle to be 2.
I've not played around with parameters at all so not sure if possible, but as I will be having more cycles to add in the future is there a way to have the calculated field based on parameters of the cycle I set e.g. cycle 4 against cycle 2?
That's works perfectly thanks very much!!!
I would have to edit the calculated field every time I want to change the cycle comparion e.g. the change from a future 4 to 2. Is there a way I can get it to work with parameters or filtering where I select the moderation cycles to compare without having to edit the calculated field?
One more thing, I seem to be running into another problem that it's not showing the correct calculation for multiple territories.
These all refer to one entity:
When I add the territory to the fixed range in the calculated field I can narrow it down to this:
But the data is still incorrect. Any thoughts?
Thanks very much again, I figured out the issue with my second question - I had to change it from SUM(IF to MIN(IF which then returned the correct calculations.
Also thanks for sending the parametrpa info .looks like I was already on the right lines.
Much appreciated .