Comparing dates in an if statement

[carotravaglini]

Hi! I need to compare 2 dates in order to count how many employees are at some time. 

I have a pivot table with month and year as dimensions.

if(date(Fecha) >= date(leg_fecing,'MM/dd/yyyy'), Count(leg_numero)- Count (leg_fecegr))

It's not working. How can i solve this?

 

[QFabian]

Hi @carotravaglini , please try something like this :

Count(if(num(Fecha)>=num(leg_fecing), leg_numero))

-

Count(if(num(Fecha)>=num(leg_fecegr), leg_fecegr))

[carotravaglini]

Hi @Qfabian, thanks for the answer. I've got some results with your reply, but wrong ones. 

[QFabian]

Hi, checking your original formula, maybe it is like this:

Count(if(num(Fecha)>=num(leg_fecing), leg_numero))

-

Count(if(num(Fecha)>=num(leg_fecing), leg_fecegr))

[carotravaglini]

Thank you @QFabian , i found a solution with this formula and your idea was really helpfull:

count(distinct(leg_numero)) - count(distinct(if([leg_fecegr.autoCalendar.Date]<[Fecha.autoCalendar.Date],leg_numero))) - count(distinct(if([leg_fecing.autoCalendar.Date]>[Fecha.autoCalendar.Date],leg_numero)))