Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Ashwinyp
Contributor III
Contributor III

Count if and filter over calculated table

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.

ApplicationIDApplication dateAGREEMENTNOSALE_DATEEmp Code
11117703-11-201869
22227007-08-2018960
333327010-12-2018960
44445510-08-201887
5555  90

 

If I select today's date (03-01-2019), it should show me the following table:

Emp CodeCount of agreements
870
900

 

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.

3 Replies
OmarBenSalem
Partner
Partner

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:

Capture.PNG

OmarBenSalem
Partner
Partner

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

 

Capture.PNG

Ashwinyp
Contributor III
Contributor III
Author

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