Distinct Count Formula

[dannyb29]

Hi All,

This should be so simple but i cannot for the life of me get this Crystal formual to work.

DistinctCount ({AVBTransactionLog.TxnId},{Users.LoginName}) where {AVBRecordTypes.Description}= "End of Shift"

Please help if you can x

 

[fisheromacse]

dunno, it is early still. off the top of my head without crystal nearby, how about:

IF {AVBRecordTypes.Description}= "End of Shift"
THEN DistinctCount ({AVBTransactionLog.TxnId},{Users.LoginName})

 

[lbass]

You would have to set up a formula like this:

if {AVBRecordTypes.Description}= "End of Shift" then
{AVBTransactionLog.TxnId} else
{@null}


...where {@null} is a new formula that you open and close without entering anything. If the TxnID is a number, wrap {@null} in tonumber().

You can then insert a distinctcount on this formula at whatever level you wish.

-LB

 

[dannyb29]

Thank you LBass that worked amazingly!

 

[dannyb29]

Hi LB,

I have used this formula for a number of different thigs within the same report which is great!
Is there a way of stating more than one product code.

What i have below works just fine.

if {AVBRecordTypes.Description}= "Top-Off Order from AVB" and {AVBTransactionLogDetails.ProductCode}= 99082 then
{AVBTransactionLog.TxnId} else
{@null}

What i would like to work but doesnt is this:

if {AVBRecordTypes.Description}= "Top-Off Order from AVB" and {AVBTransactionLogDetails.ProductCode}= 99082 or 99079 then
{AVBTransactionLog.TxnId} else
{@null}

"or 99079" throws up errors. Am I missing something or should it work?

Many Thanks

 

[fisheromacse]

you need to set the OR apart using parenthesis:

if {AVBRecordTypes.Description}= "Top-Off Order from AVB" and

(
{AVBTransactionLogDetails.ProductCode}= 99082 or {AVBTransactionLogDetails.ProductCode}= 99079
)

then
{AVBTransactionLog.TxnId} else
{@null}

 

[lbass]

OR,

if {AVBRecordTypes.Description}= "Top-Off Order from AVB" and
{AVBTransactionLogDetails.ProductCode} in [99082, 99079] then
{AVBTransactionLog.TxnId} else
{@null}

-LB

 

[dannyb29]

Thank you both!