Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

Get maximum value

Hi all,

I have a question regarding the following conditions:

I have a table:

LetterApr-14May-14Jun-14Jul-14Aug-14Sep-14
A100200300400500600
B100200300400500
C600700800900

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
Qty100100600700800900

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
Qty100200600700800900

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

Thanks in advance!

8 Replies
venkatg6759
Contributor III

Re: Get maximum value

Use pivot table

Expression firstsortedvalue(Letter,-Date)

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

Not applicable

Re: Get maximum value

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

MVP
MVP

Re: Get maximum value

maybe

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

1.png

Not applicable

Re: Get maximum value

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

Not applicable

Re: Get maximum value

Hi Lyn,

Look at my reply in your other similar thread.

Thanks,

Ram

Not applicable

Re: Get maximum value

Hi Ram,

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

for example: sum(Qty)

Thanks!

Not applicable

Re: Get maximum value

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

MVP & Luminary
MVP & Luminary

Re: Get maximum value

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.