Date Formula Needed for Distinct Count 1

[dlazarus66]

Formula function: I am working with a manual crosstab. I need a formula to say, "if date = Monday", show me a distinct count of "x" just for Monday. I have various selections taking place so I'm unable to use //{@Monday}:
if dayofweek({Date}) = 2 then 1.

I need to take that day of Monday and do a Distinct count just for that day. I tried using the DistinctCount (f1,f2,s) and put Monday for the s, but it's rejected. I created a formula just for Monday then did "Distinctcount ({qryActionsLinkedToJobs.ActionJobPKID}, {@Monday})", but it gives me a count for the entire week, and not just Monday. Any help would be much appreciated.

 

[lbass]

Create a formula {@Null} by opening and saving a new formula without entering anything.

Then create a second formula:

if dayofweek({Date}) = 2 then
{qryActionsLinkedToJobs.ActionJobPKID} else
tonumber({@null})

If the actionjobpkid is a string, then omit the tonumber().

Place this formula in the detail section and then right click on it and insert a distinctcount on it at the group and/or report level.

-LB

 

[dlazarus66]

This works perfectly!!! You're the best and thanks much.