Get previous data - last date before max(date)

[acpt]

Hi,

This is my data:

store;client;rdate;sales
58;A;09/05/2023;1
58;A;09/05/2023;1
58;A;23/05/2023;1
1;B;30/05/2023;1
1;B;30/05/2023;1
64;B;01/06/2023;1
64;B;01/06/2023;1
94;C;02/07/2023;1
94;C;02/07/2023;1
94;C;02/07/2023;1
94;C;02/07/2023;1
69;D;19/09/2023;1
69;D;19/09/2023;1
69;D;19/09/2023;1
69;D;19/09/2023;1
69;D;19/09/2023;1
69;D;27/09/2023;1

I need to create a set analyses expression to give me previous rdate - the date before rdate.
my current expression is: aggr(max({<rdate= {"<$(=max(rdate))"}>} rdate),client )  but is not working..

Last Sale Date:=max(rdate)
Previous Sales date:aggr(max({<rdate= {"<$(=max(rdate))"}>} rdate),client ) 

the result that i need is:

client	Sales	Last Sales date	Previous Sales date
A	3	23-05-2023	09-05-2023
B	4	01-06-2023	30-05-2023
C	4	02-07-2023	02-07-2023
D	6	27-09-2023	19-09-2023

thanks in advance,
ac

[MatheusC]

I didn't notice, because now you also need to return the field if it didn't exist before

In this case, you will need to work with if in your dimension expression

Previou Date

=if(rdate = aggr(only(Date(aggr(max(rdate),client ),'DD/MM/YYYY')),rdate),rdate,

aggr(only(Date(aggr(max(rdate,2),client ),'DD/MM/YYYY')),rdate))


greetings!!

[acpt]

first of all thank you very much for your reply and effort to help me, i really appreciate.

your expression seems to work... but i found some strange situation. with the first data your expression works, but if i try with data below, some previous date fails to show:

client store rdate sales
527 58 14/01/2023 1
527 58 18/01/2023 1
527 58 25/01/2023 1
5227 58 30/01/2023 1
5227 58 30/01/2023 1
5227 58 30/01/2023 1
527 58 31/01/2023 1
507 58 09/05/2023 1
507 58 23/05/2023 1
538 58 24/06/2023 1
538 58 24/06/2023 1
539 58 24/06/2023 1
539 58 24/06/2023 1
539 58 24/06/2023 1
5551 58 27/06/2023 1
538 58 09/09/2023 1
538 58 09/09/2023 1
538 58 09/09/2023 1
543 58 11/11/2023 1
527 58 24/11/2023 1
527 58 24/11/2023 1

the result (why the client 539 has no date?):  
if i clic on cell its show the date 24/06/2023... don't understand ... 😞

thanks again!!

client	max(rdate)	=sum(sales)	=if(rdate = aggr(only(Date(aggr(max(rdate),client ),'DD/MM/YYYY')),rdate),rdate, aggr(only(Date(aggr(max(rdate,2),client ),'DD/MM/YYYY')),rdate))
507	23-05-2023	2	09/05/2023
527	24-11-2023	6	31/01/2023
538	09-09-2023	5	24/06/2023
539	24-06-2023	3	-
543	11-11-2023	1	11/11/2023
5227	30-01-2023	3	30/01/2023
5551	27-06-2023	1	27/06/2023

[sidhiq91 · Accepted solution]

@acpt  Please use the below expression in your table:

For Last sales date: Max(rdate)

For Previous Sales Date: If(isnull(Max(rdate,2)),max(rdate),Max(rdate,2))

If this resolves your issue, please like and accept it as a solution.

[PrashantSangle]

don't make expression complex using aggr()

try below

take table

dimension - client

Measure 1 - Sales - Sum(Sales)

Measure 2 - Last Sales Date - max(rdate)

Measure 3 - Previous Sales Date - if(isnull(Max(rdate,2)),Max(rdate),Max(rdate,2))

Regards,

Prashant Sangle

[acpt]

Thank you very much! with a simple expression you get the job done! 5*