topic Re: Value from a max date in QlikView

Value from a max date

leocattqv — Fri, 14 Aug 2015 19:01:55 GMT

Hi All,

I am stumpped on a seemingly easy question.

I have a table that shows

Business Unit	Yr/Mon Orig	MonthEndDate	CPM
Dept 1	2015/01	1/31/2015	2,047
Dept 2	2015/01	1/31/2015	2,364
Dept 3	2015/01	1/31/2015	2,936
Dept 1	2015/02	2/28/2015	1,726
Dept 2	2015/02	2/28/2015	2,239
Dept 3	2015/02	2/28/2015	2,365
Dept 1	2015/03	3/31/2015	1,963
Dept 2	2015/03	3/31/2015	2,101
Dept 3	2015/03	3/31/2015	2,008
Dept 1	2015/04	4/30/2015	1,868
Dept 2	2015/04	4/30/2015	2,083
Dept 3	2015/04	4/30/2015	2,288
Dept 1	2015/05	5/31/2015	1,729
Dept 2	2015/05	5/31/2015	1,831
Dept 3	2015/05	5/31/2015	2,027
Dept 1	2015/06	6/30/2015	1,702
Dept 2	2015/06	6/30/2015	1,610
Dept 3	2015/06	6/30/2015	1,748

what I a trying to do is display the latest CPM for that Dept - in this case its the bottom 3 numbers.. Again, I know this is simple but can someone point me the right direction?

thanks

-L

Re: Value from a max date

swuehl — Fri, 14 Aug 2015 19:17:24 GMT

Take care that your dates are interpreted by QV as such and show a numeric representation.

Then create a straight table chart with dimension Business Unit and as expression

=FirstSortedValue(CPM, -MonthEndDate)

Re: Value from a max date

maxgro — Fri, 14 Aug 2015 19:17:27 GMT

in a chart with Business Unit as dimension,

the expression should be

FirstSortedValue (CPM, -MonthEndDate)

Re: Value from a max date

leocattqv — Fri, 14 Aug 2015 19:34:35 GMT

thank you Massimo, that worked.

thanks,

-L

Re: Value from a max date

maxgro — Sun, 16 Aug 2015 05:49:36 GMT

Please close the thread giving helpful and/or correct answers.

Thanks

Re: Value from a max date

qlikviewwizard — Sun, 16 Aug 2015 11:39:39 GMT

Hi Please close the thread giving helpful and/or correct answers.