Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

How do I count all if sales are greater than 0

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

1 Solution

Accepted Solutions
Highlighted

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

View solution in original post

10 Replies
Highlighted
Not applicable

can you send me your qvw file ?

Regards

Lathaa

Highlighted
Not applicable

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

Highlighted

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

View solution in original post

Highlighted
Creator
Creator

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



Highlighted

Try

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

HIC

Highlighted
Creator
Creator

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

Highlighted

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


Highlighted
Creator
Creator

Thank You very much for your answer!

It is working very well!

Highlighted
Contributor
Contributor

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.