Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Highest figures for each selected data quartile?

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

7 Replies
chematos
Specialist II
Specialist II

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

Not applicable
Author

Thanks.

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

chematos
Specialist II
Specialist II

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

Not applicable
Author

N

chematos
Specialist II
Specialist II

Period DATE IM Capital Ratio
0
£2027816666.86
100.00%
109/10/2012£2027816666.86
51.77%
210/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)


Not applicable
Author

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
Specialist II
Specialist II

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