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

If statement to only show results where the total count exceeds 10

I'm new to Qlik Sense and am struggling with an if statement.

In a pivot table I need to see all of the users who have submitted 10 or more quotations, if a user has submitted less than 10 quotations I do not want them to reflect on the pivot table.

Below if the expression I have managed to get to, although it does show the data is also still shows all total submissions under 10 and so does not work fully.

IF(sum({$<Quote_Submitted_Count ={1}, [Created xad] ={"1"}>} TransactionID)>10,Count({$<Quote_Submitted_Count ={1}, [Created xad] ={"1"}>} TransactionID),0)

Finally I need a measure as an expression for a simple KPI that shows the total number of users who has submitted 10 or more quotations.

Any help would be much appreciated, thank you.

1 Solution

Accepted Solutions
sunny_talwar

Not completely sure I understand the problem, but may be this in the pivot table? Removing , 0 at the end

If(Count({$<Quote_Submitted_Count ={1}, [Created xad] ={"1"}>} TransactionID) > 10, Count({$<Quote_Submitted_Count ={1}, [Created xad] ={"1"}>} TransactionID))

Again, not sure the issue with the Aggr() function, can you share a sample to see the issue?

View solution in original post

11 Replies
sunny_talwar

May be you need this:

If(Count({$<Quote_Submitted_Count ={1}, [Created xad] ={"1"}>} TransactionID) > 10, Count({$<Quote_Submitted_Count ={1}, [Created xad] ={"1"}>} TransactionID), 0)

and this for KPI

Sum(Aggr(If(Count({$<Quote_Submitted_Count ={1}, [Created xad] ={"1"}>} TransactionID) > 10, 1, 0)))

Anonymous
Not applicable
Author

Hi David,

Use below statement as a calculated dimension.

=Aggr(If(sum({$<Quote_Submitted_Count ={1}, [Created xad] ={"1"}>} TransactionID)>10, YourDimension), YourDimension)

Farrukh

Anonymous
Not applicable
Author

Thanks Sunny, the slight change does help. however any user with no submissions at all shows on the pivot.

So all users between 1 and 9 have now gone, but anyone with 0 is still there.

The field in question as 1 to denote a yes and a 0 to denote a no, not sure if this is impacting the expression?

Finally the aggr function brings back no value a - is displayed, not sure why.

Anonymous
Not applicable
Author

Hi Farrukh, thank you for responding. Is this for the simple KPI?

sunny_talwar

Not completely sure I understand the problem, but may be this in the pivot table? Removing , 0 at the end

If(Count({$<Quote_Submitted_Count ={1}, [Created xad] ={"1"}>} TransactionID) > 10, Count({$<Quote_Submitted_Count ={1}, [Created xad] ={"1"}>} TransactionID))

Again, not sure the issue with the Aggr() function, can you share a sample to see the issue?

sunny_talwar

My bad, the Aggr() was not complete... try this:

Sum(Aggr(If(Count({$<Quote_Submitted_Count ={1}, [Created xad] ={"1"}>} TransactionID) > 10, 1, 0), TransactionID))

Anonymous
Not applicable
Author

Thanks once again, so close now, and really just aesthetic to go. The Pivot table now correctly shows all users who have greater than 10 sales, anyone who does not fall into the category of 10 or more reflects as - (or Null).

I can see how you hide null values in a dimension, but cannot seem to hide null values in our measure.

Is there anyway to hide these users who do not fit the 10 or more criteria range?

Anonymous
Not applicable
Author

Thanks once again, you're certainly getting to the bottom of this a lot quicker than I could!

The count does now work, however it is counting all users as opposed to the users who hit the criteria.

So for example, when I use a filter to show date for November only the count is 8.77K. We did have 8.77K active users in November, but only 1326 of them hit the target of 10 or more sales.

Anonymous
Not applicable
Author

Managed to amend your statement slightly to show 0 instead of null and then used the addons section to hide the 0's.

So this part is now complete, thank you Sunny.

Just the simple KPI bit I am still struggling with.