Count if count is greater than

[Quentinn]

Hello, 

following question:

I want to make a statement like this:

Count (IF ([DateType] = 'SlideMade' AND count (DateType > 10) , DateType))

Of course this won't work, but how can i reformulate this? I want to count all DateType when the Datetuype is equal to 'slidemade' and when the count of datetype is more than 10 (to rule out days with very little production)

Thanks in advance!

[Andrei_Cusnir · Accepted solution]

Hello,

Perhaps you can do something similar to this:

1. I have two different datasets:

a. In the first dataset, based on your requirements, the count should be 11

b. For the second dataset, based on your requirements, the count should be Null() (Because we have less than 10 records with DataType2='SlideMade')

2. I have used the same expression to create 2 KPIs:

If(Count({<DateType1={"SlideMade"}>}DateType1) > 10, Count({<DateType1={"SlideMade"}>}DateType1), Null())

This expression states:

• Count all the records in DataType1 field, but only where DataType1='SlideMade'
• Then we are using this count in the condition and if that count is grater than 10, we show the count result
• Otherwise, we show Null()

3. The result is as follows:

As you can see the first KPI shows the number 11 and the second KPI shows Null() since the criteria is not met.

I hope that this was helpful. In case I have misunderstood the use case scenario, please elaborate in detail by providing additional details. Otherwise, if it has helped you resolve the issue, please mark it as solution to give further visibility of the other community members. 

[anat]

count({<DT={"=count({<DT={"SM"}>}DT)>10"}>}DT)

[Andrei_Cusnir]

@anat 's answer, is a great solution as it relies entirely on set analysis! I would recommend however changing the count({<DT={"=count({<DT={"SM"}>}DT)>10"}>}DT) with count({<DT={"=count({<DT={'SM'}>}DT)>10"}>}DT). Because, otherwise you will get a "Error in expression". 

[anat]

Thanks for the correction....

[Quentinn]

Your solution worked perfectly!

For some reason @anat 's soution didn't work, not sure why, thanks for the effort though!