topic Re: Top results of a running competition in QlikView

Top results of a running competition

Thu, 24 May 2012 12:59:04 GMT

Hi,

I am struggling with a chart that would present the top 5 results in a running competition over the years.

I have one table (Runners) containing all the names and gender of the competitors.

Another table (Results) contains the names together with results and years for each competition.

I am not sure how to configure dimension and expression to achieve what I want.

Regards,

Mikael

Re: Top results of a running competition

swuehl — Thu, 24 May 2012 13:08:33 GMT

Do you want to see top 5 runners per year over top 5 over all years?

If you are using QV11, both should be pretty easy using dimension limit tab in e.g. straight table properties.

Re: Top results of a running competition

Thu, 24 May 2012 13:25:44 GMT

Hi,

I would like to have the top runner over all years presented in a bar chart starting with the fastest time.

I thought this was an easy object to set up and I started with the Name as the Dimension. However, I couldn´t see what the expression would look like. The table contains all the results for all years. One runner could be represented multiple times if they have competed more than one year.

/Mikael

Re: Top results of a running competition

Thu, 24 May 2012 13:30:35 GMT

Do you want the average position of the runner or the highest position?

Top results of a running competition

Thu, 24 May 2012 13:35:54 GMT

Hi,

could you please give us an example of your data?

An xls file will be perfect.

And an example of the result you want to obtain.

Thanks a lot

Re: Top results of a running competition

Thu, 24 May 2012 13:36:05 GMT

I am looking for the fastest running times. Position is not listed in the table so it´s the fastest times that is interesting.

Re: Top results of a running competition

swuehl — Thu, 24 May 2012 13:36:44 GMT

Mikael,

if I understood your requirements correctly, you could start with dimension Name, as said.

Then use

=min(Time)

as expression to show the minimum time per Name.

Then use dimension limi tab and check 'Restrict where...' selecting 'Show only 5 smallest values'

Deselect 'Show Others' at the bottom part. You can sort by y-value in sort tab, too.

Hope this helps,

Stefan

Re: Top results of a running competition

Thu, 24 May 2012 13:47:37 GMT

Don't forget Min is a group by function so you won't see all the rows just the fastest, you may want to have

Name

Period

Min(Time)

Top results of a running competition

Thu, 24 May 2012 13:52:40 GMT

Great,

Min was the function that I was looking for. However, there is still one issue with this. One of the runners should be represented twice on the top list because the has to of the fastest running times in different years. As I use Name as Dimension and "Show only 5 smallest values" she is only represented once in the top list.

Can I solve this too?

Re: Top results of a running competition

Thu, 24 May 2012 13:55:04 GMT

Set Year as another dimension as mentioned in my previous post, once you have the multiple dimensions in it should naturally split.

You could also use something like Aggr(Min(Time),Name,Year)

Re: Top results of a running competition

swuehl — Thu, 24 May 2012 14:07:06 GMT

Create a new field in your fact table:

LOAD

Name,

Time,

Year,

dual(Name, autonumber(Name&Year)) as TopNameDim

FROM Table;

Then use this field TopNameDim instead of Name (and leaving the other settings, dimension limits untouched).

You could also create a second expression

=Year

deselect display option 'bar' and select Text on axis.

Hope this helps,

Stefan

Re: Top results of a running competition

Wed, 30 May 2012 07:41:29 GMT

Thanks Stefan,

Been away for a couple of days but this helped me completely. Clean and easy solution...

Regards,

Mikael