topic How do I count all if sales are greater than 0 in QlikView

How do I count all if sales are greater than 0

Anonymous — Wed, 18 Mar 2015 01:16:25 GMT

Hello Qlik Community,

It's my first day building a dashboard and not surprisingly I've run into a formula i can't work out.

I have created a pivot table that shows certain selected products that i want to look at. I'm now trying to determine a way to return the number of distinct accounts (accountnum) regardless of my current selections that had sales over $1.

I have tried many things but the below two expressions are the closest i could get. If i do not filter my pivot table the column total for the first formula is the number i am after (250).

Count(TOTAL DISTINCT (accountnum)) //shows total products for pivot table selection (returns 100)

Count({1}TOTAL DISTINCT accountnum) //total accounts for every product in the system, not all these had sales (returns 1000)

I might also need to build in the variable month=Feb etc if you could also help with that.

The end goal being to show the percent of total accounts that sold the preselected items.

Something like: Count(TOTAL DISTINCT (accountnum))/ some formula.

Thank you for your help,

Chris

Re: How do I count all if sales are greater than 0

Wed, 18 Mar 2015 06:58:30 GMT

can you send me your qvw file ?

Regards

Lathaa

Re: How do I count all if sales are greater than 0

Wed, 18 Mar 2015 07:00:44 GMT

Hey Chris,

If I have understood your problem correctly -

You have taken "Products" as dimension and you want to count the records with sale>0....

In this case I think you can try -

count(if(sale>0,accountnum))

or let me know if I have missed something.

Regards,

Nitesh

Re: How do I count all if sales are greater than 0

hic — Wed, 18 Mar 2015 07:04:53 GMT

My guess is that you should use

Count({$<sales={">0"}>} distinct accountnum) /

Count({1<sales={">0"}>} distinct accountnum)

That is - both the numerator and the denominator has a restriction so that only records with sales are included.

HIC

Re: How do I count all if sales are greater than 0

Anonymous — Tue, 18 Aug 2015 10:11:33 GMT

Hi,

hic

Your proposition is the best, i have one additionally question for you:

In my case the example above in set analysis is looking like that:

Count({$<KwotaW={">0"}>}KwotaW)

I want to count all fields KwotaW but for different IDs: 1, 2 and 3.

So if I choose MO_ID=1 ==> I want to have counting of KwotaW only for specific MO_ID.

It is possible to do it with function if or under specific conditions?

Thank you in advance for your help,

Best regards

Jacek Antek

Re: How do I count all if sales are greater than 0

hic — Tue, 18 Aug 2015 10:19:19 GMT

Try

Count({$<KwotaW={">0"}, MO_ID={1}>} KwotaW )

HIC

Re: How do I count all if sales are greater than 0

Anonymous — Tue, 18 Aug 2015 13:34:43 GMT

Thank you hic for swift answer,

This is working ! : Count({$<KwotaW={">0"}, MO_ID={1}>} KwotaW )

But what if i have 2 conditions: KwotaW={">0"} or KwotaW={"<0"}?

It can be done ?

Thank you for your help!

Best Regards,

Jacek Antek

Re: How do I count all if sales are greater than 0

hic — Tue, 18 Aug 2015 13:42:22 GMT

Sure!

If you want KwotaW >0 AND KwotaW <10, you can use

Count({$<KwotaW={">0<10"}, MO_ID={1}>} KwotaW )

If you want KwotaW <0 OR KwotaW >10, you can use

Count({$<KwotaW={"<0"}+{">10"}, MO_ID={1}>} KwotaW )

HIC

Re: How do I count all if sales are greater than 0

Anonymous — Tue, 18 Aug 2015 13:49:24 GMT

Thank You very much for your answer!

It is working very well!

Re: How do I count all if sales are greater than 0

Anonymous — Tue, 18 Aug 2015 14:31:06 GMT

There was an issue with my load that was causing errors in my dashboard but that was fixed and your solution worked. Thank you, Henric.

Re: How do I count all if sales are greater than 0

adellcom — Sat, 16 Mar 2019 19:40:31 GMT

Teşekkürler. Harika çözüm.