Conditional YoY Evolution in Cross Table

[Vincent Thuilot 2]

Hello

Let's say that you have a dataset made up of:

- Year (or "CAMPAIGN")

- Sales Volume

- Country

- 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.

Anyidea how to conditionally calculate YoY evolution

 

Regards

Vincent

[Kirsten Smith (she/her)]

Try wrapping this expression in a case() statement so that you can construct different expressions for each value in the Entry Type field.

 

case when 'a' then 'b' when 'c' then 'd' when 'e' then 'f' else 'x' end

[Vincent Thuilot 2]

HiThanks a lot, i tried but a CASE/WHEN statement with a "THEN [Value]" like below leads to an error...

Sum([sales Volumes]) THEN [Value] - First([Value]) OVER (NavigatePeriod([Axis.Rows],"CAMPAIGN",-1))

If you have any other idea feel free!

The tweak I have made so far is to use only rows for the categories and use the Navigate Period on Type instead of CAMPAIGN...to be continued

[Vincent Thuilot 2]

For the record, here is the tweak that I used.

It was tricky but the solution was 1) in the way the data was displayed 2) in finding the correct offset (Type and not Year)

Sum([sales Volumes]) THEN ([Value] / First([Value]) OVER (NavigatePeriod([Axis.Rows],"Type",-1))) - 1 as [Evolution %]

If I order the Type items correctly (Custom Sort), it works like a charm...

I should bring the solution to 90% of these kinds of problems...

Regards

Vincent