topic Re: Sort by occurrence in QlikView

Sort by occurrence

Mon, 02 Mar 2015 09:41:06 GMT

Hi there,

I am working on mockup flight delay data for a uni project. Every delay occurs for a reason (passengers late, plane malfunction, IT problem). I want to show a line chart with the types of delay per month, with a chart next to it displaying a top 10 of reasons (incl. their nr. of occurence). The line chart works fine but the top 10 box doesn't seem to work properly.

I'm using with the following formula:

=if((Year=('2014') and Carrier = ('Delta')), (Sum([DelayReason])))

=if((Year=2014 and Carrier = ('Delta')), (Count([DelayReason])))

Both seem to return the same result, but when I change Year to 2013 or another Carrier, nothing changes.

My ideal result would be having a box as below for every carrier:

2013	Occurence	2014	Occurence
Passenger late	1000	Plane malfunction	1000
Plane malfunction	750	IT Problem	750
IT Problem	500	Passsenger late	500
Bad weather	250	Bad weather	250
Congestion air traffic	1	Congestion air traffic	1

Thanks in advance for the help!

Re: Sort by occurrence

Anonymous — Mon, 02 Mar 2015 11:37:57 GMT

I recommend to use Year and DelayReason as dimensions, and set expression just "=sum(...)", without if statements.

In case you want to show only top10 just go to dimension limit tab (in object properties) and select show the 10 bigger values.

Hope it helps.

Re: Sort by occurrence

Mon, 02 Mar 2015 11:38:58 GMT

Your Dimensions are exactly the same, ie Year and Carrier, so the or statement is being ignored.

Use set analysis

SUM({<Year=, Carrier={'Delta'}>}DelayReason)

COUNT({<Year=, Carrier={'Delta'}>}DelayReason) )