Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
@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.
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 |
@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.
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
Thank you very much! with a simple expression you get the job done! 5*