topic Re: Find lastvalue in a chart in QlikView

Find lastvalue in a chart

tiklabaq — Wed, 06 Sep 2017 10:56:48 GMT

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

Re: Find lastvalue in a chart

marcus_sommer — Wed, 06 Sep 2017 11:01:18 GMT

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

- Marcus

Re: Find lastvalue in a chart

sunny_talwar — Wed, 06 Sep 2017 11:10:00 GMT

and to get the date, you can do this

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

Re: Find lastvalue in a chart

Kushal_Chawda — Wed, 06 Sep 2017 11:10:02 GMT

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)

Re: Find lastvalue in a chart

PrashantSangle — Wed, 06 Sep 2017 11:13:38 GMT

Hi,

take Material, data as dimension

and Expression

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

Regards,

Re: Find lastvalue in a chart

tiklabaq — Wed, 06 Sep 2017 11:19:56 GMT

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

tiklabaq — Wed, 06 Sep 2017 11:41:14 GMT

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

Re: Find lastvalue in a chart

PrashantSangle — Wed, 06 Sep 2017 11:44:23 GMT

Take Typ as dimension instead of expression.

Regards

Re: Find lastvalue in a chart

sunny_talwar — Wed, 06 Sep 2017 11:51:26 GMT

Try this

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

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