Find lastvalue in a chart

[tiklabaq]

Hi Friends,

I need your help.

I make you a example of my question:

Data:

Material	Doc	Date	Typ
MAT123	004567	01.01.2017	111
MAT123	005577	01.02.2017	125
MAT123	005777	11.02.2017	135
MAT123	005977	15.02.2017	111

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)
MAT123	11.02.2017	135

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

Thank you very much

best regards

Ibrahim

[marcus_sommer · Accepted solution]

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

- Marcus

[sunny_talwar]

and to get the date, you can do this

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

[Kushal_Chawda]

Data:

LOAD Material,

            Doc,

            date(Date) as Date,

            Typ

FROM table:

left join(Data)

LOAD Material,

           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)

[PrashantSangle]

Hi,

take Material, data as dimension

and Expression

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

Regards,

[tiklabaq]

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

[tiklabaq]

Ok, one question:

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

example:

Material	Doc	Date	Typ
MAT123	004567	01.01.2017	111
MAT123	005577	01.02.2017	125
MAT123	005777	11.02.2017	135
MAT123	005977	11.02.2017	135

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

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

Result:

Dimension(Material)	Expression 1 ( Date )	Expression 2 (TYP)
MAT123	-	-

[PrashantSangle]

Take Typ as dimension instead of expression.

Regards

[sunny_talwar]

Try this

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

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