Jump to content
We've recently updated our Privacy Statement, available here. ×

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


Adi Lavy

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...