How do I count all if sales are greater than 0

[Anonymous]

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

[#2]

can you send me your qvw file ?

Regards

Lathaa

[#3]

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

[hic · Accepted solution]

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

[Anonymous]

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

[hic]

Try

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

HIC

[Anonymous]

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

[hic]

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

[Anonymous]

Thank You very much for your answer!

It is working very well!

[Anonymous]

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.