Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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