Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ndeeleysww
Creator
Creator

Count if...

Hello,

I'm successfully counting the number of alarms using set analysis to create a line chart:

Count({$<Date={'>=$(=Date($(vTodaysDate)))'}, alarm_status={3}>} AlarmCount)

My line chart has a fixed reference line showing all the alarms that are higher than a burst point of 12:

Image1.png

My manager now wants a count of where the alarms for a given date are above 12. I think I should be able to do this with a count if, but i can't get it to work:

count(if(Count({$<Date={'>=$(=Date($(vTodaysDate)))'}, alarm_status={3}, DataSource={'Alarms'}>} AlarmCount) >12, AlarmCount)

Any ideas on the syntax I should be using?

Cheers

Neil

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Maybe like this:

sum(aggr(if(

Count({$<Date={'>=$(=Date($(vTodaysDate)))'}, alarm_status={3}>} AlarmCount)>12, Count({$<Date={'>=$(=Date($(vTodaysDate)))'}, alarm_status={3}>} AlarmCount)

),TenMinuteInterval))

Replace TenMinuteInterval with the name of your x-axis dimension.


talk is cheap, supply exceeds demand

View solution in original post

10 Replies
Gysbert_Wassenaar

Perhaps like this:

count({$<Date={'>=$(=Date($(vTodaysDate)))'}, alarm_status={3},TenMinuteInterval={"=Count({$<Date={'>=$(=Date($(vTodaysDate)))'}, alarm_status={3}>} AlarmCount)>12"}>} distinct TenMinuteInterval)


talk is cheap, supply exceeds demand
ndeeleysww
Creator
Creator
Author

Sorry Gysbert, that didn’t work…

Gysbert_Wassenaar

Post a small qlik sense document that illustrates the problem.


talk is cheap, supply exceeds demand
ndeeleysww
Creator
Creator
Author

Unfortunately I can’t as its stored on a secure server to which I don’t have access!

Basically I’m counting the number of alarms by a date. I only want to count the number of alarms by date where the count is greater than 12 – otherwise, I don’t need the count.

This is so ultimately I can create a % total where the alarm count per date is greater than 12.

Gysbert_Wassenaar

Then create a new small qlik sense document that illustrates the problem.


talk is cheap, supply exceeds demand
ndeeleysww
Creator
Creator
Author

Hi Gysbert,

I can’t – I don’t have Sense Desktop, and therefore cannot create a file I can send you. Our systems are all locked down here, I`m afraid…

Neil

Gysbert_Wassenaar

Maybe like this:

sum(aggr(if(

Count({$<Date={'>=$(=Date($(vTodaysDate)))'}, alarm_status={3}>} AlarmCount)>12, Count({$<Date={'>=$(=Date($(vTodaysDate)))'}, alarm_status={3}>} AlarmCount)

),TenMinuteInterval))

Replace TenMinuteInterval with the name of your x-axis dimension.


talk is cheap, supply exceeds demand
Not applicable

count(if(Count({$<Date={'>=$(=Date($(vTodaysDate)))'}, alarm_status={3}, DataSource={'Alarms'}>} AlarmCount) >12, AlarmCount) )

maybe just missing the closing bracket?

Gysbert_Wassenaar

No, it's nesting aggregation functions so it needs to use the aggr function.


talk is cheap, supply exceeds demand