4 Replies Latest reply on Oct 3, 2017 7:38 AM by Stefan Hanotin

# Index recalculation when filtering

Hi all,

I have attached a packaged workbook of customer sales that has an Index() based on how the table is sorted. The table is Sorted based on a parameter which sorts either by Sales quantity or Invoice Date. The Invoice Date has to be converted to an Integer to sort. There are 2 filters, one based on the Financial Week and another based on the Index() determining if the sales quantity is in the Top N (parameter).

I want to be able to use both filters at the same time, but the Index isn't recalculated when you apply the first filter.

Instructions to reproduce:

1. Observe Index.
2. Change Limit filter to "Last 2 weeks only".
3. Observe Index which isn't recalculated.
4. Change Top N filter to "Top N Deals"

I would like for the Index to be recalculated when either filter is used.

Any help would be greatly appreciated.

Thanks,

Stefan.

• ###### 1. Re: Index recalculation when filtering

Hi Stefan,

Your index() needs to be the first pill on your columns;

• ###### 2. Re: Index recalculation when filtering

Hi Neil,

Thanks for the reply but that doesn't solve the problem. If you reset all filters and start by limiting by the last 2 weeks only, you can see that the index starts at 2. I want it to start at 1 and count as far as 4 in this case as there are 4 sales in the last 2 weeks.

Thanks,

Stefan.

• ###### 3. Re: Index recalculation when filtering

Hi Stefan,

The main issue is your "last 2 weeks" calculation....when you use a table calc (like index or window_max) it applies the results to the table generated by table...which is not necessarily what you see in the view. So Index() of 2 is the second row in the table and not the second row you see (if that makes sense).

If you change your formula to this:

Then you get the results you are looking for;

Workbook Attached

1 of 1 people found this helpful
• ###### 4. Re: Index recalculation when filtering

Thanks a million Neil. This is exactly what I was looking for.