Count if and filter over calculated table

[Ashwinyp]

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.

[OmarBenSalem]

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:

[OmarBenSalem]

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,', ')

[Ashwinyp]

@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!