8 Replies Latest reply: Jun 24, 2014 12:24 AM by jagan mohan rao appala

# 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 [A]May-14 [B]Jun-14 [C]Jul-14 [C]Aug-14 [C]Sep-14 [C]
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 [A]May-14 [A]Jun-14 [C]Jul-14 [C]Aug-14 [C]Sep-14 [C]
Qty100200600700800900

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

• ###### Re: Get maximum value

Use pivot table

Expression firstsortedvalue(Letter,-Date)

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

• ###### Re: Get maximum value

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

• ###### Re: Get maximum value

maybe

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

• ###### Re: Get maximum value

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

• ###### Re: Get maximum value

Hi Lyn,

Thanks,

Ram

• ###### 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!

• ###### Re: Get maximum value

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

• ###### Re: Get maximum value

Hi,

Try like this

Temp:

CrossTable(Month, Qty)

[

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:

*,

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.