topic If statement to only show results where the total count exceeds 10 in App Development

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

Anonymous — Fri, 16 Dec 2016 10:50:58 GMT

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.

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

sunny_talwar — Fri, 16 Dec 2016 12:23:50 GMT

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)))

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

Anonymous — Fri, 16 Dec 2016 12:30:20 GMT

Hi David,

Use below statement as a calculated dimension.

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

Farrukh

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

Anonymous — Fri, 16 Dec 2016 13:47:56 GMT

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.

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

Anonymous — Fri, 16 Dec 2016 13:48:49 GMT

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

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

sunny_talwar — Fri, 16 Dec 2016 16:47:33 GMT

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?

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

sunny_talwar — Fri, 16 Dec 2016 16:48:11 GMT

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))

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

Anonymous — Mon, 19 Dec 2016 09:57:55 GMT

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?

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

Anonymous — Mon, 19 Dec 2016 10:02:05 GMT

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.

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

Anonymous — Mon, 19 Dec 2016 10:51:54 GMT

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.

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

Anonymous — Mon, 19 Dec 2016 11:52:55 GMT

Thanks once again Sunny, after playing the expression for a while I managed to make it work.

You've been a great help.

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

sunny_talwar — Mon, 19 Dec 2016 11:58:33 GMT

Awesome, I am glad I have been able to offer some help