topic Count if / only qlik sense in App Development

Count if / only qlik sense

joeybird — Fri, 29 Sep 2017 11:49:25 GMT

trying both ways , but wrong ..can I have some help please

I want to only ever count customers that only order on a specific month, and not on any other month of the year

e.g August

I have tried this way but don't work

Count(DISTINCT if([Month]='Aug', CustomerID))

I have heard you can do this with an only command

only({<[Month]={'Aug'}>} CustomerID)

but how do you count using an only?

please help

Re: Count if / only qlik sense

swuehl — Fri, 29 Sep 2017 12:52:44 GMT

Maybe use

Count({<CustomerID = e({<Month = e({<Month = {'Aug'}>}) >})>} DISTINCT CustomerID)

Re: Count if / only qlik sense

swuehl — Fri, 29 Sep 2017 12:54:35 GMT

Count({<CustomerID = e({1<Month -= {'Aug'}>}) >} DISTINCT CustomerID)

Re: Count if / only qlik sense

swuehl — Fri, 29 Sep 2017 12:56:41 GMT

And if you like the Only() idea maybe

Count({<CustomerID = {"=Only(Month)= 'Aug' "} >} DISTINCT CustomerID)

Re: Count if / only qlik sense

joeybird — Fri, 29 Sep 2017 13:00:12 GMT

this method worked the best

thanks again team !

Re: Count if / only qlik sense

jonvitale — Fri, 29 Sep 2017 13:02:39 GMT

Stefan,

This is interesting - I'm trying to learn more about the E() and P() functions. Can you explain what you are trying to do here? My novice take on it is - moving from the innermost E outwards - that you are excluding all sales from the set that occurred in august, and then you are excluding all the remaining months. Seems like this would leave you with an empty set.

What am I missing?

Re: Count if / only qlik sense

joeybird — Fri, 29 Sep 2017 13:42:18 GMT

stefan

Count({<CustomerID = {"=Only(Month)= 'Aug' "} >} DISTINCT CustomerID)

I am trying to add stop a user selecting the dimension Department

i have tried adding the following

Count({< Department =, CustomerID = {"=Only(Month)= 'Aug' "} >} DISTINCT CustomerID)

but the measure is still being affected

any ideas please

Re: Count if / only qlik sense

swuehl — Fri, 29 Sep 2017 13:54:08 GMT

Looking at the first expression:

1) Make a virtual selection of 'Aug' in Month field

2) Select excluded on virtual list box Month

Now this should result in all possible Month values except Aug being selected

3) Select excluded on virtual CustomerID list box

This should result on selection of CustomerID that don't have a relation to the Month selected (all Month except Aug).

Hm, maybe this selects also the Customer ID that don't have a relation to any Month (including Aug).

And using especially e() could get troublesome if other selections needs to be taken into account.

So Joanna is probably right that the third expression is the easiest expression to understand and maintain.

Re: Count if / only qlik sense

jonvitale — Fri, 29 Sep 2017 14:04:18 GMT

Thank you Stefan.

Re: Count if / only qlik sense

jonvitale — Fri, 29 Sep 2017 15:46:03 GMT

This seems unexpected.

Can you describe what happens when you select a specific department?

Re: Count if / only qlik sense

swuehl — Fri, 29 Sep 2017 15:49:46 GMT

The expression search will be affected by the selection state, so maybe

Count({<Department=, CustomerID = {"=Only({<Department=>} Month)= 'Aug' "} >} DISTINCT CustomerID)

Re: Count if / only qlik sense

joeybird — Mon, 02 Oct 2017 09:33:01 GMT

you cracked it

thank you Stefan / Team

Kind Regards