Omniscope does not play well with a field that is dual-purpose, where two (or more) metrics are used within the same field, since they have different ranges and do not graph well together.

Normally we would suggest you Re-pivot, selecting the Metric field at the first prompt, and Visits at the second. This is a one-way operation that restructures your data to create:

Region Brand Month(period) Visits Spend
GB brand 1 Jan 0008 200 500,000
FR brand 1 Jan 0008 203

You might also be able to do this at source, instead of using Omniscope. But if this is unworkable in other views, consider having two IOK files.

If you need this in one file without changing your data structure, you may be able to create a formula field which adapts the Value field into a common range (e.g. percentage-based, per metric) allowing it to be graphed on the Y axis. Alternatively, in the Bar view, pane by Metric, and click the "Normalise panes" button.

Hi Steve,

I guess I can't use the table structure I currently have and I don't especially want to re-pivot since all my other charts work well off the original table structure, so I have merged in another table to feed these charts -is that you were suggesting the two iok files?

So I am using the second file that has the metrics as columns in the table - I can set the scatter chart up perfectly well this way, but I would like the scatter chart to work off the sidebar menus in the same way that the other charts in the tab do (these charts are based on the original table) but if I do this the chart contains no data - the metric that I would like to change is "country" which is a column in both the original and the secondary table. -I'm not sure if this makes sense or not, and I'm probably doing something stupid, but do you know if I can make this work?

Also - this is the only time I have ever merged in another file - when I update the file, it only updates the original file -can I make it update the merged in file also?

Thanks again

Tim

I meant use two separate IOK files which would need to be opened separately to view the two different structures. However, merging might also work. How did you merge the data in? Did you concatenate or join? If you're using the Merge command in Omniscope, and you subsequently refresh the data from source, it should repeat the last merge.

Is this what you have?

Region / Brand / Month(period) / Metric / Value
GB / brand 1 / jan-08 / visits / 200
FR / brand 1 / jan-08 / visits / 203
GB / brand 1 / jan-08 / spend / 500000

Region / Brand / Month(period) / Visits / Spend
GB / brand 1 / Jan 0008 / 200 / 500,000
FR / brand 1 / Jan 0008 / 203 / null

merged together as:

File / Region / Brand / Month(period) / Metric / Value / Visits / Spend
Original / GB / brand 1 / jan-08 / visits / 200 / null / null
Original / FR / brand 1 / jan-08 / visits / 203 / null / null
Original / GB / brand 1 / jan-08 / spend / 500000 / null / null
Repivoted / GB / brand 1 / Jan 0008 / null / null / 200 / 500,000
Repivoted / FR / brand 1 / Jan 0008 / null / null / 203 / null

...where you filter by File to select the data structure you want; dimension filters such as Brand work on both regions of the file?

Or have you gone for this:

Region / Brand / Month(period) / Metric / Value / Visits / Spend
GB / brand 1 / jan-08 / visits / 200 / null / null / 200 / 500,000
FR / brand 1 / jan-08 / visits / 203 / null / null / 203 / null
GB / brand 1 / jan-08 / spend / 500000 / null / null / 200 / 500,000

...where the data is unchanged from the original, except the repivoted data has been joined back as new columns with duplicates?

I can't see how repivoting isn't suitable. Can you give me an example of a chart configuration that requires the original data structure?

Hi Steve

I concatenated the two files. So I imagine that the data must look like this:

File / Region / Brand / Month(period) / Metric / Value / Visits / Spend
Original / GB / brand 1 / jan-08 / visits / 200 / null / null
Original / FR / brand 1 / jan-08 / visits / 203 / null / null
Original / GB / brand 1 / jan-08 / spend / 500000 / null / null
Repivoted / GB / brand 1 / Jan 0008 / null / null / 200 / 500,000
Repivoted / FR / brand 1 / Jan 0008 / null / null / 203 / null

On my filter sidebar - I don't have a 'file' option - I assumed by selecting metrics (I.e. the column that don't exist in the original dataset) omniscope would ignore the original data set and only use the re-pivoted file.

I don't want to use two IOK files since I want to create these scatter charts underneath other charts that feed off the original structure [in the same tab].

Basically I tried uploading the whole file as a re-pivoted table and using the old charts I had set up with this new data - I found I couldn't make certain things work, i.e.
>>Pie chart, 'paned' 4 times by brand that has five metrics feeding it - which works fine if the metrics are in one column, but not so well if they are in five columns.
>>Also - off the same data - a stacked bar chart over time with the same five metrics feeding into the chart, but split by brand - again I don't think this works if the metrics are in different columns (although I accept this could probably be made to work if I summed the five metrics in excel and added a new column in the data)
>>Line chart with two metrics on it - could I select two metrics to plot (with time on the x axis) on this if the two metrics were in different columns?

[Incidentially I want to change the data in all these fields by allowing the user to change country by using the country metric in the side bar - I don't think this matters but thought I should mention it]

Thanks again

Tim

Tim,

I'm afraid I'm beginning to run out of ideas. Did you try my 2nd data structure suggestion? I'm not sure if it would work for you since it has duplication. Here it is again:

Region / Brand / Month(period) / Metric / Value / Visits / Spend
GB / brand 1 / jan-08 / visits / 200 / null / null / 200 / 500,000
FR / brand 1 / jan-08 / visits / 203 / null / null / 203 / null
GB / brand 1 / jan-08 / spend / 500000 / null / null / 200 / 500,000
(where the data is unchanged from the original, except the repivoted data has been joined back as new columns with duplicates)

Regarding your questions on the other data structure you've chosen (concatenated): You're pushing the boundaries of Omniscope by trying to work with 2 data structures at once. Some of the views have more power to handle these situations, and some don't.
>> Pie chart: does not support multiple measures, so you can't achieve the effect you want within one view. You can create more pie charts, one of each metric, if need be. Will Bars instead of Pies suffice? Read on...
>> Stacking: Bar view *does* support multiple measures, so pick each of your metrics using the Measure drop-down, then choose to Pane by Measures. This should achieve exactly the effect you want: a pane for each measure, with stacking of whatever you want inside each pane. Or, stack by measures, and pane by a category - you have that flexibility.
>> Line chart: Again, not possible in the Graph view. You can have multiple Graph views open simultaneously, if you want. Or you can use a formula in Omniscope (why use Excel for this?) to aggregate the metrics in some form.

Steve

Hi Steve,

Thanks for all your help with this - I did get there in the end with concatenating!

Tim

Good to hear - Steve