Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
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
Highlighted
chematos
Valued Contributor II

Re: Highest figures for each selected data quartile?

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

Re: Highest figures for each selected data quartile?

Thanks.

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

chematos
Valued Contributor II

Re: Highest figures for each selected data quartile?

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

Re: Highest figures for each selected data quartile?

N

chematos
Valued Contributor II

Re: Highest figures for each selected data quartile?

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

Re: Highest figures for each selected data quartile?

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
Valued Contributor II

Re: Highest figures for each selected data quartile?

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