topic Re: Counting with Set Analysis in QlikView

Counting with Set Analysis

Tue, 30 Sep 2014 15:38:22 GMT

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?

Re: Counting with Set Analysis

jyothish8807 — Tue, 30 Sep 2014 15:48:37 GMT

Hi Sam,

Hows the count=3 and not 5??

Regards

Re: Counting with Set Analysis

Tue, 30 Sep 2014 15:53:07 GMT

Hi, KC.

It's 3 because the two entries on 1/5/2014 cancel each other out. Basically, the item was charged in error, and reversed.

Re: Counting with Set Analysis

jyothish8807 — Tue, 30 Sep 2014 15:57:17 GMT

Still we have a count of 4 ? Can you explain a bit more about your requirement?

REgards

Re: Counting with Set Analysis

JonnyPoole — Tue, 30 Sep 2014 16:12:55 GMT

I think you want 4 records in this case

In this chart I used the following formula for Amount. It checks for procedurechargecategories and the total for that date.

Note that if the charge was debited and creditted on the same day, the total for that day is 0. Is that a good assumption ?

You may also need to total on more dimensions than <Date> in your actual data set but this works for the sample. Post a longer sample if you need to get further clarification

if( sum( total <Date> if(ProcedureChargeCategory<250, Amount)) <> 0 , sum( if(ProcedureChargeCategory<250, Amount) ))

Re: Counting with Set Analysis

Tue, 30 Sep 2014 16:22:41 GMT

Hi again, Jonathan.

Yes, you're both correct. The answer should be 4, not 3. Don't you love it when the accountant fails at basic arithmetic?

In your expression, the sum of Amount is correct. But what I need is a Count. I tried changing the second IF to COUNT, but I get 6, not 4.

Re: Counting with Set Analysis

jyothish8807 — Tue, 30 Sep 2014 16:43:30 GMT

Try getting the count in a textbox.

if( sum( total <Date> if(ProcedureChargeCategory<250, Amount)) <> 0 , count(distinct if(ProcedureChargeCategory<250, Amount) ))

Regards

Re: Counting with Set Analysis

JonnyPoole — Tue, 30 Sep 2014 16:50:38 GMT

For count i added this expression into the same chart.

if( sum( total <Date> if(ProcedureChargeCategory<250, Amount)) <> 0 and ProcedureChargeCategory<250, 1)

Then on the total's , change it from "expression total" to 'Sum of Rows"

Re: Counting with Set Analysis

jyothish8807 — Tue, 30 Sep 2014 17:01:56 GMT

Try getting grand total in a text box,that will be more meaningful. Just a suggestion .Above one is good

Regards

Re: Counting with Set Analysis

Tue, 30 Sep 2014 18:57:45 GMT

Jonathan:

I left out an important piece of the puzzle here. The table I posted is my data model. My chart (a straight table) needs to look like this:

Account	ProcedureChargeCategory	Count
R456789	120	4
R456789	250
R456789	362
R456789	985
R678910	122	2
R678910	255
R678910	621

The only lines that have a value in the Count column are those with a ProcedureChargeCategory < 250. The rest would be either blank or 0. So, the accumulation happens in the chart expression.

Re: Counting with Set Analysis

JonnyPoole — Tue, 30 Sep 2014 19:10:23 GMT

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 ?

Re: Re: Counting with Set Analysis

Tue, 30 Sep 2014 20:13:30 GMT

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.

Re: Re: Re: Counting with Set Analysis

JonnyPoole — Tue, 30 Sep 2014 20:40:01 GMT

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

Re: Re: Counting with Set Analysis

Tue, 30 Sep 2014 20:48:32 GMT

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

Re: Re: Counting with Set Analysis

Tue, 30 Sep 2014 20:56:25 GMT

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.

Re: Re: Re: Counting with Set Analysis

JonnyPoole — Tue, 30 Sep 2014 23:42:41 GMT

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

)

Re: Re: Re: Counting with Set Analysis

Wed, 01 Oct 2014 13:39:31 GMT

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?

Re: Counting with Set Analysis

JonnyPoole — Wed, 01 Oct 2014 13:58:18 GMT

Super !