Hi there,
Given a sales transaction listing, for each sales staff, I would like to identify if the last sales date for a particular product was more than 30 days ago.
Referring to the attached workbook, Trade No 105, 106 and 107 should be flagged as the last sales date was more than 30 days ago for that particular product and staff. I managed to do this using the following steps:
Step 1: Created a calculated field called "Last Transaction Date" to identify the previous transaction date for each transaction
Step 2: Created another field called "Alert" that informs me whether there are cases where the last sales date for a particular product by a sales staff was more than 30 days ago.
Step 3: Created this view.
Using this, I can use filters to zoom in to the transactions that meet my criteria.
However, the issue now is I would like summarise the above into a table similar to below, where I know Alex has 2 sales transactions in Feb where the last sales date was more than 30 days ago. In my real life data, I have many staff and months and this summary table will be very helpful. Is there a way to do this? Maybe there is a need to do steps 1 to 3 above differently in the first place?
Thank you.
Regards,
Jeric
Hi Jim,
Appreciate you taking the time to explain.
For "product date" tab, is there a way to show only the number of transactions that meets the condition where the last sales date for a particular product by a sales staff was more than 30 days ago (i.e. "Alert" calculated field = "Alert")? Right now, it's just showing all the transactions:
In my head, the table should look something like this instead:
Unless there is alternative method where I don't use "LOOKUP(ATTR([Transaction Date]),-1)" for the Last Transaction Date?
Thanks,
Jeric
