Counting with Set Analysis

[#1]

I need some help with counting in an expression.

My data set looks like this:

Account	Date	ProcedureChargeCategory	TransactionProcedureID	Amount
R456789	1/1/2014	120	120004	500
R456789	1/2/2014	120	120004	500
R456789	1/3/2014	120	120004	500
R456789	1/4/2014	120	120004	500
R456789	1/5/2014	120	120004	500
R456789	1/5/2014	120	120004	-500

On 1/5/2014, the item was charged in error, and reversed, resulting in a zero amount for that day. I need to count the number of charges that were not reversed.  In this case, the correct count = 3, not 5.

The only items I want to count are those where ProcedureChargeCategory<250, so I can count the total using this expression:

     if(ProcedureChargeCategory<250,count(ProcedureChargeCategory))

But I'm at a loss as to how to account for the reversed charge to arrive at the correct total.  I'm guessing it's a set analysis problem, which is definitely not my strong suit.

How would you solve this problem?

[JonnyPoole]

I think you lost me.  I'm not sure what is being counted here. In this new table,   R456789 has 4 records, 1 is below <250.  But the count says 4.   For R678910 there are 3 records, 1 is below 250, but the count says 2.

?

Can you provide a comprehensive data set and desired result  ?

[#3]

I was afraid of that.  My fault, not yours.

I can't upload my entire data model because it's too big, and there are Federal privacy laws in play.  So, here's an anonymized, abbreviated version.

The count for Account R123456 is correct.  It should be 4.  However, the count for R456789 is incorrect. It reads 6, but should be 4.  In the data model, there are offsetting entries on 1/5/2014 in ProcedureChargeCategory 121.

[JonnyPoole]

Ok. I think counting the transactions is easier if the data has unique identifier.

So i changed your script to add a 'recordnumber'  (1,2,3 etc...)

LOAD

  RecNo() as RecordNumber,

  AccountNumber,

    Amount,

    ProcedureChargeCategory,

    ServiceDate,

    TransactionProcedureID

FROM

(biff, embedded labels, table is Sheet1$);

Then I adjusted the count expression in the chart as follows. Note that there is a built in assumption here. THat any amounts with negative values, indicate 2 records need to be deducted from record count for that account number.

  count( distinct total <AccountNumber>

  if(ProcedureChargeCategory<250  ,RecordNumber))

  -

  (count( distinct total <AccountNumber>

  if( ProcedureChargeCategory<250 and Amount < 0, RecordNumber)))*2

[#5]

Thanks, Jonathan.  I'll play with that and report back.

[#6]

Maybe I'm missing something.  The QVW had a slightly different expression for Count, that resulted in zero for every line.  When I replaced it with the expression in your post, it gets the count right, but shows it for every ProcedureChargeCategory, not just those <250.

I'm honestly not nit-picking. The output has to match a State-mandated format for a crucial survey.

[JonnyPoole · Accepted solution]

Probably my bad. I changed something at the last sec and probably forgot to upload with the changes in.

To show the count only where the code is < 250 the formula is a bit simpler.

if( ProcedureChargeCategory<250,

  count( distinct RecordNumber)

  -

  count( distinct if( Amount < 0, RecordNumber))*2

)

[#8]

Thanks again, Jonathan.  I think that did the job.  I need to do some more verification with the full data set, but this seems like it'll get the job done.

Have I mentioned how much help this community is? 

[JonnyPoole]

Super !