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

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
Creator III
Creator III

Use pivot table

Expression firstsortedvalue(Letter,-Date)

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

Not applicable
Author

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

maxgro
MVP
MVP

maybe

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

1.png

Not applicable
Author

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

Not applicable
Author

Hi Lyn,

Look at my reply in your other similar thread.

Thanks,

Ram

Not applicable
Author

Hi Ram,

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

for example: sum(Qty)

Thanks!

Not applicable
Author

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

jagan
Luminary Alumni
Luminary Alumni

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.