Hi, please find attached demo file that deals with both Base Index and Moving Index scenarios. Variable 'Base Period' will enable you to change the date, resulting in different payment value used for the index base calculation. (obviously this will work only when you select dates for which Payment information is available).
Formulas used...
Base Index:
[Payment]/
SUBSET_FIRST([Payment], SUBSET([Date], [Base Period]))

Moving Index:
[Payment]/
SUBSET_SUM([Payment],SUBSET([Rank Date], [Rank Date]-1,"="))

Formula result fields are then formatted as % values.

Hello,

This is exacly what I need, and I think I was almost here with it.

However I would like to be able to move the "base period" and for the moving base to start at that date. So the "payment" becomes base (rank 1) at the date set. Is this possible?

Dave

Not sure I understand the question fully, but will try to answer. Use of variable in the Base Index formula will enable you to select the base value, used for comparison with all other values.
In the Moving Index scenario, the base is relative, and changing all the time, from one period to another. There is an option to use n-2 or n-3 etc instead of n-1 period, but the principle remains the same.
Index values are always linked to date, so date is the variable to rank...

Great, I will have a play and see if this will do it.

Thanks

Dave

Ah ha,

Almost what I need, but the rank date vaule should move with the sliders. So rank 1 should be the minimum date set. Is this possible?

Dave

Rank Date field is there only as a reference for the Moving Index formula. Even if you remove first few values, by using the Rank slider, the Index values will not be affected, because all that matters in calculation is the order, rather than which value is No1.

No, I need to be able to pick where the start date is, for the moving formula, using the date sliders. And that should then be 100% and only look at the data below that date, and ignore every thing else.

Dave

You can do that now... (disregard the very first index, that will be populated referring to now invisible period). You can also then convert the field to static values and change manually that first index to 100%.

When I move the date slider to a new start date, say 14/02/2012, I want that date in the filtered subset to now be rank 1, and to calculate as Index= 100% and for everything below to be based on that filtered start date. - Dave

do you see what I mean? So we move the dat slider to any date we liked, and that would become the 100% marker and every thing else would be a index of that?

In the demo file above, you got both scenarios (moving/base index), both responding to date change.
If you still have issues, please give us a call and we could do join.me session.

It would seam that this is ovley complex. Omniscope could really benifit from the ability to do on the fly calculations on just the selected data.

You could do this by placing the table in the Content View and selecting 'Filtered data' as subset.
In order to give you specific recommendation we would have to see a sample of your data. You can replace any sensitive names/clients/categories with A, B, C...

That sounds intresting. How do I put it into 'Filtered data' view?

Create Content View with an empty table, with only few rows/columns.
Copy/paste formula into the field. (you can use one of the formulas from the demo file)
Select 'filtered data' subset at the top.

ah I have it in filtered data. But I am still unsure how this will work. What is contect view?

Ok got it, just playing with this now.

No, this also does not seem to do it.

I will send you a file as an example.

I have create a little exmaple based on yours. So I would like to be able to use the devices to select a date between, and a roue and a rate. And for the first price selected to become the base, at 100. And for every thing after to be a index of that.

Any ideas, Lovley people?

The Base index formula responds to the change of base date, and after that every Value (in this case [Payment] is compared to that value, and expressed as %.
Filtering affects which rows will be displayed, but not the calculation, which still works. For base period where value is 200, all other 200 value fields will have 100% index, for example.

Yeah but I cant use that mathod as I need to be able to change any thing in the devices and for the index to be reflected

How will the change in field filter affect the index calculation?
Do you wish to calculate Base index values for different combinations of 2 field filters (in which case you end up with several series of index values, for each combination of factors, equal to number of permutations)??
For that you will need to widen the Subset formula from :

[Payment]/SUBSET_FIRST([Payment], SUBSET([Date], [Base Period]))

To:

[Payment]
/
SUBSET_FIRST([Payment], SUBSET3([Date], [Month], [Category], [Base Period],[Month],[Category]))

This second formula will work and produce different set of index numbers, using base value from the selected base period, for combinations of [Month] and [Category] fields.

Hope this is what you wanted, otherwise please explain.
I'm hoping to close this post by Christmas :-)

I dont want to use a base period. Its not possible. As some rates will go from 1990 till 2000 and others from 2000 on. It would be to complicated.

What I would like to do is simple.

I want to be able to select data using exisitng devices, and for the first bit of filtered data to be the index that all other data should be based on.

Dave - We call this type of problem 'dynamic normalisation', and it is currently not possible to do this in Omniscope strictly formulaically. We do plan to add this in a future version of the Graph and Bar/Line View, to enable the movement of a slider line to indicate the base period value, and to autmatically re-base all the display values to this value.

I have re-classified this as an Idea. Please send us a sample data set we can use to fully understand your typical data structure and filtering options, and to use for testing this feature as developed.

As an immediate next step...let's investigate whether custom JavaScript in the Content View can be used....