I am building a KPI table and want to populate it with data from a lead manager I have created so I know what the sales funnel KPI's are for each month.

Funnel is Leads, spoken to, apointment, intake, sale

I have a contacts table and a leads tabel.

In the contacts table i use the date that I have created the contact.

In the leads tabel I use the funnel field to see what funnel status the contact is in.

 

So retreiving all leads was easy 

let m := Maand;
let j := Jaar;
count((select Contacten)[mnd = m and jr = j].Id)

 

But now I want to do a select in two table and find the contacts with the status "spoken to"

for now I have:

let m := Maand;
let j := Jaar;
let ff := 2;
count((select Contacten).'Lead manager'[Contacten.'Lead manager'.'Funnel fase' = ff].Id)

What is missing here is the WHERE date part

So somthing like WHERE funnel fase is 1 AND month is 1 AND year is 2020.

 

One other thing is that the contacts with the status apointment, intake and sale are also contact I have spoken to.

So the WHERE statement should be even more complicated...

 

So something like 

WHERE funnel fase is 2 OR 3 OR 4 OR 5 AND Month = 7 AND Year = 2020

How should I create a query like that in Ninox?

 

Thanks in advance!