Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
ndeeleysww
Not applicable

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

Tags (2)
1 Solution

Accepted Solutions
Gysbert_Wassenaar
Not applicable

Re: Count if...

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
10 Replies
Gysbert_Wassenaar
Not applicable

Re: Count if...

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
Not applicable

Re: Count if...

Sorry Gysbert, that didn’t work…

Gysbert_Wassenaar
Not applicable

Re: Count if...

Post a small qlik sense document that illustrates the problem.


talk is cheap, supply exceeds demand
ndeeleysww
Not applicable

Re: Count if...

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
Not applicable

Re: Count if...

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


talk is cheap, supply exceeds demand
ndeeleysww
Not applicable

Re: Count if...

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
Not applicable

Re: Count if...

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

Re: Count if...

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

maybe just missing the closing bracket?

Gysbert_Wassenaar
Not applicable

Re: Count if...

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


talk is cheap, supply exceeds demand