Distinct count

MT10 — Wed, 15 May 2024 11:06:07 GMT

can someone advise me a formulae for below case, i want to count the no.of invoices with invoice amount <>0

case(

Invoice Amount > 0, distinct(

Invoice Number),

'0'

)

Re: Distinct count

RADSr — Wed, 15 May 2024 14:07:01 GMT

Is your data at the grain of invoice number? If so, then you can use:

sum(case(inv_amt <> 0, 1, 0 ) )

If it's not ( e.g. your reporting is at the invoice line level ) then you want something like:

sum(case(inv_amt <> 0, 1, 0 ), groupby(inv_number) )

If you are doing a KPI insight then just distinct(inv_no) and then filter out amounts = 0

HTH - if it doesn't get you there you can reply w/ more detailed info about your use case and we'll get it.