I have the task of comparing two years and the way I want to do it is to compare the top grossing event in one year to the top grossing event in the other year. My thought was to create two charts, rank the events by revenue and then compare them in a third chart, but while creating the third chart I realized I was out of my league. If I do it this way is it possible to bring the #1 grossing event from both years into the third chart and get a difference between them? I basically want to show the rank, event, total and then the difference between the #1 in the first year to the #1 in the second year and so on...
Is it possible to do this in one chart?
I've attached a simple example of what I've started with, but can't figure out how to get the comparison to work.
I see how you did this, but I don't fully understand what it's doing.
In the load script you wrote "LOAD DISTINCT autonumber(Event) as RankID Resident Prices". Would you mind breaking down what it does? How does it rank the entire event rather than the individual 'sales'?
It just creates a counter, with as many elements as distinct Events exists.
I need this for the rank dimension (there might be other solutions, but I failed to get these running right now).
The dimension is just a number 1 - 7 in this setting. The ranking and filtering of the expression results is all done in the expressions itself (that's different to your first version, where you rank and filter the event in the dimension).
I needed to create a dimension that this independent of a certain Event.
When I implement this into my production data it runs really slow. Is there a way to speed it up? The load works just fine, but the application itself updates very slow. I have over 5,000 events in my data and over 20,000,000 records of sales data which I assume is causing the slowness.
I see. Using advanced aggregation in combination with the RankID data island is not showing best performance.
I can't see a better chart based solution right now. If your rank table does not need to be sensitive to selections (e.g. selections in Product), you can use a script based approach, calculating the rank in the load script.
edit: Do you need to show all 5000 events in the comprison table? If not, it might be possible reduce the work needed to create the table.
I'm actually only showing about 40 events from each year so 80 in total at any one time. I think the load script approach might be my best bet. Thanks for your help Stefan.
