Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
ApplicationID | Application date | AGREEMENTNO | SALE_DATE | Emp Code |
1 | 111 | 77 | 03-11-2018 | 69 |
2 | 222 | 70 | 07-08-2018 | 960 |
3 | 333 | 270 | 10-12-2018 | 960 |
4 | 444 | 55 | 10-08-2018 | 87 |
5 | 555 | 90 |
If I select today's date (03-01-2019), it should show me the following table:
Emp Code | Count of agreements |
87 | 0 |
90 | 0 |
As of now, I am only able to count the number of agreements for employees in the last 3 months using this code:
count({< [SALE_DATE]={">=$(=date(Max(SALE_DATE)-90,'DD/MM/YYYY'))<=$(=max(SALE_DATE))"} >}
DISTINCT(AGREEMENTNO))
This does not show me employees who do not have a sale date in the last 3 months. Please help with this.
In the script:
create 2 flags as follow:
table:
load ApplicationID, AGREEMENTNO, date(Date#(SALE_DATE,'DD-MM-YYYY')) as Date, EmpCode,
if(AGREEMENTNO>0,1,0) as FlagSales Inline [
ApplicationID, AGREEMENTNO, SALE_DATE, EmpCode
1, 77, 03-11-2018, 69
2, 70, 07-08-2018, 960
3, 270 ,10-12-2018, 960
4, 55, 10-08-2018 ,87
5, , , 90
];
left Join(table)
load EmpCode,if(Today()-max(Date)<=90,1,0) as Flag3Months Resident table Group by EmpCode;
(Please, adapt it to what u have)
then, create a table and use this measure;
count(distinct{<FlagSales={0}>+<Flag3Months={0}>} EmpCode)
result:
If u want to have a list of employees in a text box, u do as follow:
='Employees with no sales in the last 3 Months are : '&Concat(distinct {<EmpCode={"=count(distinct{<FlagSales={0}>+<Flag3Months={0}>} EmpCode)=1"}>} EmpCode,', ')
@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!