topic Conditional count if in QlikView

Conditional count if

ecabanas — Tue, 17 Nov 2015 17:29:31 GMT

Hi,

I'm creating a table with the margin of the orders_:

SALESID MArgin

A 10

B -5

C 7

Where Margin is a calculated field: Sales - Cost

I wanna know how many orders are posite or negative and I applied this formula, but it give me an error:

Count(Distinct IF([Margin]>='0', SALESID))

any knows why?

Many thank's

Eduard

Re: Conditional count if

sinanozdemir — Tue, 17 Nov 2015 17:31:56 GMT

How about this:

Count({<[Margin] = {">=0"}>} Distinct SALESID)

I also realized that in your formula you are using '0' as text, not as a number 0

Re: Conditional count if

sunny_talwar — Tue, 17 Nov 2015 17:31:57 GMT

Try it without the single quotes

and are you counting SALESID or ORDERID? Make sure to use the correct field name

=Count(Distinct If(Margin >= 0, SALESID))

=Count(Distinct If(Margin >= 0, ORDERID))

Re: Conditional count if

ecabanas — Tue, 17 Nov 2015 17:38:24 GMT

did not works 😞

Re: Conditional count if

ecabanas — Tue, 17 Nov 2015 17:40:45 GMT

I'm counting SalesId, sorry, but the formula did not works returns to me independently if Margin is positive or negative

Re: Conditional count if

sunny_talwar — Tue, 17 Nov 2015 17:41:59 GMT

Margin is a field which is getting calculated in the script? We both are assuming that it is, if it is not, then we might need to use other fields.

Re: Conditional count if

ecabanas — Tue, 17 Nov 2015 17:43:58 GMT

Hi Sunny

No, the margin is calculated in the table not in the script

Dimension: SalesID

Margin= Sales - Cost

Many thank's

Re: Conditional count if

sunny_talwar — Tue, 17 Nov 2015 17:45:48 GMT

Try this:

=Count(Distinct If((Sales - Cost) >= 0, SalesID))

Re: Conditional count if

maxgro — Tue, 17 Nov 2015 17:50:11 GMT

count( {$ <SALESID={"=Sales>Cost"}>} SALESID)

Re: Conditional count if

rwunderlich — Tue, 17 Nov 2015 19:31:38 GMT

The Margin chart column may not be used in a aggregation function count(). You will need to repeat the expressions that computed the Margin.

-Rob