Get Last Date's value for Each Product

microwin88x — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have the following table:

Product	Year	Month	Period	Sales	Area
Product1	2015	January	201501	484.12	2134
Product1	2015	February	201502	484.12	253
Product1	2015	March	201503	484.12	46
Product1	2015	April	201504	484.12	215
Product2	2015	May	201505	1888.07	463
Product2	2015	June	201506	1888.07	12
Product2	2015	July	201507	1888.07	261
Product4	2015	August	201508	1482.62	2376
Product4	2015	September	201509	1482.62	262
Product4	2015	October	201510	1482.62	124
Product5	2015	November	201511	1788.22	58
Product5	2015	December	201512	1788.22	456
Product5	2016	January	201601	1788.22	589
Product5	2016	February	201602	1788.22	5634
Product5	2016	March	201603	-	-
Product6	2016	April	201604	829.06	3732
Product6	2016	May	201605	829.06	234
Product6	2016	June	201606	756.44	24
Product6	2016	July	201607	756.44	567
Product6	2016	August	201608	756.44	76
Product7	2016	September	201609	756.44	126
Product7	2016	October	201610	1800.32	567
Product7	2016	November	201611	-	-
Product8	2016	December	201612	1800.32	4563
Product8	2017	January	201701	472.02	3454
Product8	2017	February	201702	472.02	7457
Product9	2017	March	201703	472.02	243
Product9	2017	April	201704	472.02	123
Product9	2017	May	201705	1040.86	523
Product10	2017	June	201706	1040.86	2348
Product10	2017	July	201707	1040.86	123
Product10	2017	August	201708	-	-
Product11	2017	September	201709	907.73	123
Product11	2017	October	201710	907.73	56
Product12	2017	November	201711	907.73	1234
Product12	2017	December	201712	907.73	125

What I need is to create a table with the last date for each product with the corresponding Sales and Area.

(*) Keep in mind that the last date's value could be NULL. In that case, I should bring the value before that.

Something like this:

Product	Year	Month	Period	Sales	Area
Product1	2015	April	201504	484.12	215
Product2	2015	July	201507	1888.07	261
Product4	2015	October	201510	1482.62	124
Product5	2016	February	201602	1788.22	5634
Product6	2016	August	201608	756.44	76
Product7	2016	October	201610	1800.32	567
Product8	2017	February	201702	472.02	7457
Product9	2017	May	201705	1040.86	523
Product10	2017	July	201707	1040.86	123
Product11	2017	October	201710	907.73	56
Product12	2017	December	201712	907.73	125

Do you know how could I do that?

Note: I need to do it on a chart table with expressions!!!

Thank you!!!

Re: Get Last Date's value for Each Product

sunny_talwar — Thu, 30 Nov 2017 20:22:07 GMT

What all are your dimensions and what all are the expression? I am assuming Product is the only dimension and rest all as expression?

microwin88x — Thu, 30 Nov 2017 20:26:16 GMT

Yes, that's it.

Anyway, in the end I only need to show:

Product | Sales | Area

With the corresponding last date.

sunny_talwar — Thu, 30 Nov 2017 20:28:23 GMT

May be this

Dimension

Product

Expression

Year(Date#(Max({<Sales = {'*'}>}Period), 'YYYYMM'))

Month(Date#(Max({<Sales = {'*'}>}Period), 'YYYYMM'))

Max({<Sales = {'*'}>}Period)

FirstSortedValue({<Sales = {'*'}>}Sales, -Period)

FirstSortedValue({<Sales = {'*'}>}Area, -Period)