Formula question

[clsaulsberry]

I am trying to write a formula that counts certain orders.

Fields  : Status Flag, Order Number and Case Number

The status flag tells me what is going on with the case numbers for each order.  i am trying to count the number of orders that have case numbers with a status flag of "30" but also has a status flag of "00"   

Status Flag

00

01

10

30

60

95

[sunny_talwar · Accepted solution]

May be this

Count(DISTINCT {<[Order Number] = p({<[Status Flag] = {'30'}>})*p({<[Status Flag] = {'00'}>})>} [Order Number])

[vishsaggi]

HI Sunny

can't we just write like this, just wondering

= Count(DISTINCT {< [Status Flag] = {'30', '00'} >} [Order Number])

[jayanttibhe]

Hi Guys,

What about this :

If we need Selections of [Status Flag]to be effective on the Set Expression then (Sunny's expression is the the same - I just simplified - I guess)

Count({< [Order Number]= P({< [Status Flag] = {'30', '00'} >})>}Distinct [Order Number])

If we DONT need Selections of [Status Flag] to be effective on the Set Expression then

Nagaraju your expression

Count(DISTINCT {< [Status Flag] = {'30', '00'} >} [Order Number])

[sunny_talwar]

That would include all rows where Status flag is 30 or 00... I am not sure if the OP wants that or if he wants all the Order Numbers where Status Flag is 30 and 00

[vishsaggi]

Got it.

[sunny_talwar]

If we need Selections of [Status Flag]to be effective on the Set Expression then (Sunny's expression is the the same - I just simplified - I guess)

Count({< [Order Number]= P({< [Status Flag] = {'30', '00'} >})>}Distinct [Order Number])

I don't think they are the same thing... from what I understand your's will include the Order Number if Status Flag is 30 or 00, where as the one I provided should include only those Order Numbers where the Status Flag is both 30 and 00

[jayanttibhe]

You are right Sunny - mine is OR condition  and yours is the Set intersection. My bad

[clsaulsberry]

thank you

[clsaulsberry]

thank you i wanted all to include both statuses not either or statuses.  you formula worked correctly for me.