Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
diannefrost
Contributor
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 ExampleExcel Example

1 Reply
diannefrost
Contributor
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

image.png