Count if...

[ndeeleysww]

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:

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

[Gysbert_Wassenaar]

Perhaps like this:

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

[ndeeleysww]

Sorry Gysbert, that didn’t work…

[Gysbert_Wassenaar]

Post a small qlik sense document that illustrates the problem.

[ndeeleysww]

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.

[ndeeleysww]

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 · Accepted solution]

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.

[#9]

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.