8 Replies Latest reply: Sep 6, 2017 7:51 AM by Sunny Talwar

# Find lastvalue in a chart

Hi Friends,

I make you a example of my question:

Data:

MaterialDocDateTyp
MAT12300456701.01.2017111
MAT12300557701.02.2017125
MAT12300577711.02.2017135
MAT12300597715.02.2017111

Now I want to see in a table chart for all materials the last date and related Typ, but only if Typ is 125 or 135.

Result:

Dimension(Material)Expression 1 ( Date )Expression 2 (TYP)
MAT12311.02.2017135

How can I get this Information. I've tried it with aggr, but it does not work.

Thank you very much

best regards

Ibrahim

• ###### Re: Find lastvalue in a chart

Try it with: firstsortedvalue({< Typ = {125,135}>} Date, -Date)

- Marcus

• ###### Re: Find lastvalue in a chart

Thank you Marcus,

sometimes you sit far too long in front of the computer and the simplest things do not fall into one.

Finding the date is easy, but to the related type was my problem, because only with max it would not be done here. So you have found the solution.

My expression: firstsortedvalue({< Typ = {125,135}>} Typ, -Date)

Thank you

and also thanks to the others, but I did not want to solve it in the script, so I already had a solution for this, my question was to solve it in a expression.

This forum is just great

• ###### Re: Find lastvalue in a chart

Ok, one question:

I don't get a value if I have more Documents per date

example:

MaterialDocDateTyp
MAT12300456701.01.2017111
MAT12300557701.02.2017125
MAT12300577711.02.2017135
MAT12300597711.02.2017135

Expression1: firstsortedvalue({< Typ = {125,135}>} Date, -Date)

Expression2: firstsortedvalue({< Typ = {125,135}>} Typ, -Date)

Result:

Dimension(Material)Expression 1 ( Date )Expression 2 (TYP)
MAT123--
• ###### Re: Find lastvalue in a chart

Take Typ as dimension instead of expression.

Regards

• ###### Re: Find lastvalue in a chart

Try this

Expression1: Max({< Typ = {125,135}>} Date)

Expression2: firstsortedvalue(DISTINCT {< Typ = {125,135}>} Typ, -Date)

• ###### Re: Find lastvalue in a chart

and to get the date, you can do this

Date(Max({<Typ = {125, 135}>} Date))

• ###### Re: Find lastvalue in a chart

Data:

Doc,

date(Date) as Date,

Typ

FROM table:

left join(Data)

date(max(Date)) as Date

1 as isLastDate

Resident Data

where match(Typ,'125','135')

group by Material;

Dimension:

Material,

Date

Expression:

=only({<isLastDate={1}>}Typ)

• ###### Re: Find lastvalue in a chart

Hi,

take Material, data as dimension

and Expression

Only({<date={"\$(=max(date))"}>}Typ)

Regards,