I am trying to identify the exhaustive list of employees who do not have any sales in the last 3 months.
I have a date filter (Sale date) which is sort of a 'running date' and based on the date selected in the Filter Pane, the table should show me employees who did not have any sale for 3 months prior to that. The employees who have an application but have not converted to a sale also should come up.
If I select today's date (03-01-2019), it should show me the following table:
Count of agreements
As of now, I am only able to count the number of agreements for employees in the last 3 months using this code:
@OmarBenSalem Thanks for your response. However, I am unable to apply this to my case as I have 2 tables which are joined automatically during LOAD to extract my information.
For example, Table 1 contains ApplicationID, AGREEMENTNO, Emp code and ~40 more columns, while Table2 contains AGREEMENTNO, SALE_DATE and ~30 other columns. Both tables are joined on AGREEMENTNO.
It would be better if there's a way in which I can write the code on the chart itself rather than during LOAD. If it has to be done only during LOAD, which table should I join as per your "left join" condition?
Also, in this code if(Today()-max(Date)<=90,1,0) as Flag3Months I don't want it to be based on today's date. It should be based on any SALE_DATE that I select from the filter. Thanks!