Set Analysis Count If

diannefrost — Fri, 12 Apr 2019 15:05:04 GMT

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

Re: Set Analysis Count If

diannefrost — Fri, 12 Apr 2019 18:41:34 GMT

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

topic Set Analysis Count If in QlikView

Set Analysis Count If

Re: Set Analysis Count If