topic Re: Get maximum value in QlikView

Get maximum value

Mon, 23 Jun 2014 15:14:48 GMT

Hi all,

I have a question regarding the following conditions:

I have a table:

Letter	Apr-14	May-14	Jun-14	Jul-14	Aug-14	Sep-14
A	100	200	300	400	500	600
B		100	200	300	400	500
C			600	700	800	900

I need to create a table where I have to output the latest value (latest value is base on Letter (A<B<C<D,.....)

The output should be:

	Apr-14	May-14	Jun-14	Jul-14	Aug-14	Sep-14
Qty	100	100	600	700	800	900

How do I achieve this? Please take note that my selection of letters can differ. I select A and C it should output something like this:

	Apr-14	May-14	Jun-14	Jul-14	Aug-14	Sep-14
Qty	100	200	600	700	800	900

The month selected also changes. How to achieve this? How can I only get the latest value?

Thanks in advance!

Re: Get maximum value

venkatg6759 — Mon, 23 Jun 2014 15:18:28 GMT

Use pivot table

Expression firstsortedvalue(Letter,-Date)

Caluclated Dimension (Date &' [' & Qty & ']')

Re: Get maximum value

Mon, 23 Jun 2014 15:42:51 GMT

Please do take note that those are not just numbers. they are computed values. Sum(QTY)

Re: Get maximum value

maxgro — Mon, 23 Jun 2014 19:12:36 GMT

maybe

subfield(concat(if(len(trim([Sep-14]))>0,[Sep-14]),'@', -match(Letter, 'A','B','C')), '@')

Re: Get maximum value

Tue, 24 Jun 2014 00:59:55 GMT

Hi thanks for the reply. please take note that I only have one field for the month. Not separated months

Re: Get maximum value

Tue, 24 Jun 2014 01:15:01 GMT

Hi Lyn,

Look at my reply in your other similar thread.

Thanks,

Ram

Re: Get maximum value

Tue, 24 Jun 2014 01:57:58 GMT

Hi Ram,

Thanks for the reply! how about if I have a computation inside the formula?

for example: sum(Qty)

Thanks!

Re: Get maximum value

Tue, 24 Jun 2014 02:01:09 GMT

Look at my latest attachment. Mark mine as the correct answer if you're happy.

Re: Get maximum value

jagan — Tue, 24 Jun 2014 04:24:15 GMT

Hi,

Try like this

Temp:

CrossTable(Month, Qty)

load * inline

[

Letter, Apr-14, May-14, Jun-14, Jul-14, Aug-14, Sep-14

A ,100 ,200, 300, 400, 500, 600

B ,0 ,0,100, 200 ,300 ,400 ,500

C ,0,0 ,600 ,700, 800, 2500

];

Data:

Load

If(Previous(Month) <> Month OR RowNo() = 1, 1, 0) AS IsLatest

RESIDENT Temp

WHERE Qty > 0

Order by Month, Letter Desc;

Drop table Temp;

Use this as expression in chart

Sum({<IsLatest={1}>} Qty)

Regards,

Jagan.