topic Re: Comparing ranked sales in QlikView

Comparing ranked sales

Thu, 17 Oct 2013 17:08:59 GMT

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.

Re: Comparing ranked sales

swuehl — Thu, 17 Oct 2013 17:42:28 GMT

Maybe like attached?

Re: Comparing ranked sales

Thu, 17 Oct 2013 17:50:22 GMT

This is very close. Can the other events be included as well? If I were to do it in excel it would look something like the table below:

Rank	Event YR 1	YR 1 Total	Event YR 2	Y2 Total	Difference
		10370		10546	-176
1	Comp	2230	Comp	4040	-1810
2	CompC	2038	CompB	2030	8
3	CompA	2024	CompC	2022	2
4	CompB	2020	CompA	2000	20
5	Comp2	1820	Comp3	230	1590
6	Comp4	238	Comp2	224	14

Re: Re: Comparing ranked sales

swuehl — Thu, 17 Oct 2013 20:56:19 GMT

Finally...

Hope this helps,

Stefan

Re: Comparing ranked sales

Thu, 17 Oct 2013 21:32:25 GMT

Stefan,

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'?

Re: Comparing ranked sales

swuehl — Thu, 17 Oct 2013 21:39:30 GMT

No, this does not rank anything.

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.

Does this make things more clear?

Re: Comparing ranked sales

Thu, 17 Oct 2013 21:58:53 GMT

Thanks Stefan, I think I understand what it's doing.

Re: Comparing ranked sales

Fri, 18 Oct 2013 15:33:10 GMT

Stefan,

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.

Any ideas are appreciated.

Re: Comparing ranked sales

swuehl — Thu, 24 Oct 2013 11:39:20 GMT

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.

Re: Comparing ranked sales

Thu, 24 Oct 2013 14:00:15 GMT

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.

Re: Re: Comparing ranked sales

swuehl — Thu, 24 Oct 2013 14:19:11 GMT

This version tryies to minimize the calculation load based on the number of ranks you need to display (variable vMaxRank in script).

Haven't really tested if this improves or impairs the performance, just give it a try.

Re: Re: Comparing ranked sales

swuehl — Thu, 24 Oct 2013 14:31:26 GMT

And this does a script based pre-aggregation and ranking.

Should have the best performance (and expressions are much more simple), but you are losing the ability to filter dynamically by making additional selections).