8 Replies Latest reply: Apr 8, 2011 8:11 AM by Gerhard Laubscher

# 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.

• ###### Chart with Count If Statement

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

Cheers - DV

• ###### Chart with Count If Statement

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

• ###### Chart with Count If Statement

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.

Cheers - DV

• ###### Chart with Count If Statement

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

Good luck!

Cheers - DV

• ###### Chart with Count If Statement

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,

• ###### Chart with Count If Statement

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

Thank you!

• ###### Chart with Count If Statement

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

• ###### Chart with Count If Statement

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.