Conditional YoY Evolution in Cross Table
Let's say that you have a dataset made up of:
- Year (or "CAMPAIGN")
- Sales Volume
- EntryType (Final, Forecast, AP, OP, Estimate)
I am trying to find a way to calculate YoY evolution at country level with a condition based on the Entry Type: If we have "Final" as entry type, we compare with the total sum "Final" from the previous year for the given country.
Then, if we have "Forecast", "OP" or "AP" as an entry type, we compare as well with the total sum "Final" from the previous year. At the moment it does not work because Spotfire is looking for a "Forecast" or "AP"/"OP" entry on the previous year (for that country) which does not always exist and would not be accurate.
Last, if we have "Estimate" as an entry type, we compare with "Forecast" from the previous year.
The formula i am currently using to calculate the different YoY is:
Sum([Sales Volumes]) THEN [Value] - First([Value]) OVER (NavigatePeriod([Axis.Rows],"CAMPAIGN",-1))
It does the job for "Final" entry type, however it gives nothing or comapre to the wrong benchmark if something else is showing as Entry type.
Any idea how to conditionally calculate YoY evolution?