Highest figures for each selected data quartile?

[#1]

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

[chematos]

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

[#3]

Thanks.

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

[chematos]

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

[#5]

N

[chematos]

Period	DATE	IM	Capital	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)

[#7]

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?

[chematos]

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