How to correct a value using another value in a different row while considering catagories

[Adi Lavy]

Hi all!

I'm new to Spotfire and been trying to solve this problem....

In the following table there is sales data for two days in each week in each quarter. The value of sales for each of these days needs to be corrected by subtracting the value for the factor for that week.

For example - 1st quarter, week1, Saturday has sales=20, I would like to subtract 3 (which is the value of the factor for that week).

One important consideration is that this solution cannot assume that the number of observations is fixed. It could be that in the 3rd quarter there will be data for more than 2 weeks and more than 2 days in each week.

 

I would appericiate any help!!!

[Fabian Duerr]

Step 1 is a simple IF Function:

If([Day]="factor",[sales])Step 2 is to combine with anaggregation function (like FIRST, but MIN, MAX, AVG and so on would give same result...). Your group is Qrt and Week. Grouping/aggregation is done with an OVER function:

First(If([Day]="factor",[sales])) OVER ([Qrt],[Week]) So here's the result (you only need Step 2):

 

Now just subtract this column from sales column

[Adi Lavy]

Works like a charm! Thank you so much!!