Announcements
cancel
Showing results for
Did you mean:
Contributor

## Set Analysis Count If

Please help, I am struggling with trying to get a daily count based on this formula I am using in a separate table chart. I need to count the number of Yes and No in a selected time period:
=if((Interval([Actual Time]/24, 'hh:mm:ss'))-(Interval(LunchDesignation/24,'hh:mm'))>.0006944,'No','Yes')

This issue is that the data for lunch in the data set includes all breaks, so I have to find that breaks over 27 minutes, as there are people who take different lunch periods (30min, 45min, and 60min). I am thinking I can do this in set analysis with a count if, but I cannot seem to get it figured out. I attached my sample data.

Something like this that I did in Excel, count the number of Yes and No

Excel Example

Contributor
Author

I figured it out. I took it in steps, created a variable for lunch vLunch=([Actual Activity]='Break' AND [Actual Time]>=0.42

and a variable for lunch period vLunchPeriod=(Interval([Actual Time]/24, 'hh:mm:ss'))-(Interval(LunchDesignation/24,'hh:mm'))

Then on my graph:

Yes=count(DISTINCT if(\$(vLunch) AND \$(vLunchPeriod)<.0006944,MetricDate))

No=count(DISTINCT if(\$(vLunch) AND \$(vLunchPeriod)>.0006944,MetricDate))

and the counts work

Community Browser