Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gerhardl
Creator II
Creator II

Chart with Count If Statement

Hi,

I have the following fields in an account extract (there is a row per customer):

  • Number of purchases for the cycle
  • Valueof purchases for the cycle
  • Number of paymentsfor the cycle
  • Value of paymentsfor the cycle

Every customer in the database who has not yet made a purchase will have a zero value in the two purchase fields.

I want to create a chart table that shows the number of customers who has made a purchase during the cycle.

I'm new to QV - in excel I would use something like =CountIf("number of purchases for the cycle" <> 0)

Please can someone help me to do this in QV, I am lost with these expressions.

1 Solution

Accepted Solutions
IAMDV
Luminary Alumni
Luminary Alumni

Use this one...

COUNT( {$<[Value of purchases for the cycle]-={0}>} [Value of purchases for the cycle] )

Please use set analysis. You can use COUNT or SUM for the same. This might seem to be little complex initially, however if you break the expression it is pretty simple.

Let me explain this one...

COUNT({$<[Field Name] -= {Condition}>} [Field Name])

You can use " -=" to negate a specific value. We are excluding zero in our case.

I hope this helps.. let me know if you need anything else.

Please mark the post answered if this is what you need.

Cheers - DV

View solution in original post

8 Replies
IAMDV
Luminary Alumni
Luminary Alumni

Gerhardl - Please can you post the QV document with some sample data? We can help you with these expressions.

Cheers - DV

gerhardl
Creator II
Creator II
Author

Hi DV,

I've uploaded a file with 100 records.

I would like to see the number of customers who have made a purchase during the month. So value or number of purchases must be > than 0.

Thanks,

G

IAMDV
Luminary Alumni
Luminary Alumni

Use this one...

COUNT( {$<[Value of purchases for the cycle]-={0}>} [Value of purchases for the cycle] )

Please use set analysis. You can use COUNT or SUM for the same. This might seem to be little complex initially, however if you break the expression it is pretty simple.

Let me explain this one...

COUNT({$<[Field Name] -= {Condition}>} [Field Name])

You can use " -=" to negate a specific value. We are excluding zero in our case.

I hope this helps.. let me know if you need anything else.

Please mark the post answered if this is what you need.

Cheers - DV

IAMDV
Luminary Alumni
Luminary Alumni

I am also attaching a tutorial on Set Analysis. I thought you might be interested to read this one...

Good luck!

Cheers - DV

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/674/3617.QlikLearn_2D00_Set_2D00_Analysis_2D00_Presentation.pdf:550:0]

gerhardl
Creator II
Creator II
Author

Hi DV,

That doesn't work - it still returns a chart with all 120,000 customers. But I've confused myself so much now with expressions and dimensions, I might just be doing something wrong.

Could you create the chart in the sample file I sent you and attach it?

Could it be that the "0" we want to exclude must be in the exact format as in the table? I'm clueless, obviously.

Thanks,

gerhardl
Creator II
Creator II
Author

Disregard that post - it does work, just has to be 0.00

Thank you!

IAMDV
Luminary Alumni
Luminary Alumni

No problem! I am glad you got it working. I can't upload QV documents from my system. However, let me know if you need anything else. I'll try to explain in more detail.

Happy learning!

Cheers - DV

gerhardl
Creator II
Creator II
Author

I will take you up on that - I have no experience in QV (nor IT), so everything is a struggle.

Should I start a new forum thread - I already have a new question?

I would like to know how to create a pie chart showing one field as a percentage of another fiel.

I have a field called 'OTB' (which is available credit) and another field called 'Credit Limit' - I would like to know what percentage of the total credit limit for all customers (so Sum[credit limit]) is available to them.

So if the sum of all the customers' CL is 10 million, and the sum of all their Available OTB is 3 million, then I want a pie chart showing the 10 million, with 30% of it representing OTB.