5 Replies Latest reply on Sep 27, 2017 4:35 AM by Steven Ming

# How to increment a field based on whether or not the value of a column changes to a different entry?

Hello all,

Currently I'm at a loss on how I can use LOOKUP, IF, AND and OR statements to implement a function to do the following:

The function should examine whether the value of INDEX has changed or not, and if it hasn't, add the current price to the running total for a particular index. In the end the Running Total for a particular Index must be the entry for each row that has that particular INDEX. This is shown in the table below.

INDEX
PriceDelivery HourRunning Total for Price for each INDEX
SE5110
SE5210
TS214
TS224
• ###### 1. Re: How to increment a field based on whether or not the value of a column changes to a different entry?

You could just do a MAX({FIXED [INDEX] : SUM([Price])})

• ###### 2. Re: How to increment a field based on whether or not the value of a column changes to a different entry?

My apologies, I did not mention that the Price field is already aggregated. This is the max price that corresponds to each of the indexes during that delivery hour.

• ###### 3. Re: How to increment a field based on whether or not the value of a column changes to a different entry?

Ah,

If that's the case then try, SUM({FIXED [INDEX] : [Price]})

• ###### 4. Re: How to increment a field based on whether or not the value of a column changes to a different entry?

Ah sorry, I made a silly mistake and didn't think about how you're going to show the data in your grid format.

You could also accomplish this by using a table calculation of:

WINDOW_SUM([Price])

Then right click the measure and select 'Edit Table Calculation':

Then select 'Specific Dimensions'

'At the level' = 'Deepest'

Then for 'Restarting Every', select [INDEX]

1 of 1 people found this helpful
• ###### 5. Re: How to increment a field based on whether or not the value of a column changes to a different entry?

Wow this is perfect, Thanks