topic Re: Highest figures for each selected data quartile? in QlikView

Highest figures for each selected data quartile?

Tue, 16 Oct 2012 10:33:36 GMT

I have four columns in a straight table. Dimensions: Date, IM, Capital. Expression: Ratio (IM/Capital)

I need to display only the values with the highest IM figures from each quartile of data being selected as well as the current value.

It would need to look something like this:

Period	Date	IM (£m)	Capital (£m)	Ratio
1	19/04/2012	1,222.013	53,305.558	0.023
2	16/05/2012	1,220.502	53,305.558	0.023
3	04/06/2012	1,111.183	53,305.558	0.021
4	05/07/2012	1,110.466	53,305.558	0.021
Current	16/07/2012	1,005.004	53,305.558	0.019

I went for straight table but now I am not sure it's the right decision. Any ideas how to produce the above table in Qlikview? Thanks

Re: Highest figures for each selected data quartile?

chematos — Tue, 16 Oct 2012 12:17:02 GMT

You need to use IM and Capital as expressions.

Try this:

expr 1: max(IM) ---> Using IM as label

expr 2: Capital ---> Using Capital as label

expr 3: IM/Capital

hope this helps

Re: Highest figures for each selected data quartile?

Tue, 16 Oct 2012 12:24:44 GMT

Thanks.

If I display Capital as expression, it shows - , instead of data. How would I gwet Capital to show too?

Re: Highest figures for each selected data quartile?

chematos — Tue, 16 Oct 2012 12:29:38 GMT

Try this:

only({< IM= {'>=$(=max(IM)'} >} Capital)

I´m thinking that may be you need to do the same with Date field

if this don´t work, may be you could upload your file.

You could also use FirstSortedValue

Try this and tell me

Re: Highest figures for each selected data quartile?

Tue, 16 Oct 2012 12:40:47 GMT

Re: Highest figures for each selected data quartile?

chematos — Tue, 16 Oct 2012 12:48:59 GMT

Period	DATE	IM	Ratio
0		£2027816666.86	100.00%
1	09/10/2012	£2027816666.86	51.77%
2	10/10/2012	£2010070896.35	48.23%

try this expressions:

/*date expression*/

=aggr(firstsortedvalue(DISTINCT -IM,Date),Period)

/*capital expression*/

=aggr(firstsortedvalue(DISTINCT -IM, Capital),Period)

Re: Highest figures for each selected data quartile?

Tue, 16 Oct 2012 12:52:35 GMT

Period field is not loaded, it is added to the table by using rowno() so I cannot use it expressions, or is there some other way around it?

Re: Highest figures for each selected data quartile?

chematos — Tue, 16 Oct 2012 13:04:49 GMT

Yes, you need to build a relational model with a master calendar so your dates will be related to the calendar and you will have their quarters. There are a lot of examples of master calendars, so it would be fine you start with that