Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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