Creating a YoY column in Spotfire crosstable

[Mohit Chandra]

Hi All,

I am new to spotfire and am having difficulty to achieve a functionality. I want to create a YoY column in a cross table. The table does not have any timeframe column in it but I need to provide a date range filter in the visualization. For eg. here is how a sample data for me would look like :

Date Department Gate Sales

12-Dec-18 A A1 10

13-Dec-18 A A2 12

1-Jan-18 A A3 15

12-Dec-17 A A2 14

13-Dec-17 A A4 13

12-Dec-18 B B1 10

13-Dec-18 B B2 12

1-Jan-18 B B4 15

12-Dec-17 B B1 14

13-Dec-17 B B2 11

 

Now, i want to create a table summarizing sales as

 

 

 

 

Department

Sales

Sales YoY

 

 

A

22

-5

 

 

B

22

-3

 

 

 

I want to give the user to select the date range in the filter and we should be able to get the YoY for the corresponding date range in the sales YoY column.It is not straightforward to create an LY column in the base table as the gate numbers might change in the same department between this year and last year.

Let me know if there is a way to do this. Thanks a lot in advance.

[Khushboo Rabadia]

As you are applying filter, your cross table plot will have only that part of data and you can have YOY custom expression like sum(case when Year([Date])=2018 then [sales] end)-sum(case when Year([Date])=2017 then [sales] end)