topic Re: How to count distinct ignoring a single dimension? in QlikView

How to count distinct ignoring a single dimension?

Wed, 09 Sep 2015 17:20:13 GMT

I have a table which looks like this:

Claim State Payment_Type Amount

1 PA Expert 10

1 PA Loss 15

2 PA Expert 5

2 PA Loss 25

2 PA Legal 20

I have a stacked bar chart with state on the X-axis and payment_type stacked on the y axis. I want my expression to be the average amount per claim. So, for Payment_Type = Legal it should show (20 + 0) / 2 = 10 since there are 2 claims in PA.

I tried sum(Amount) / Count( distinct Claim) but the denominator only counts rows with a value, so for Legal this would yield (20) / 1 = 20.

I need to somehow ignore the Payment_Type dimension.

Re: How to count distinct ignoring a single dimension?

sunny_talwar — Wed, 09 Sep 2015 17:31:13 GMT

I see 10 for legal:

Re: How to count distinct ignoring a single dimension?

maxgro — Wed, 09 Sep 2015 17:36:41 GMT

try this

Avg(alt(Amount,0))

sum(Amount) / Count(Claim)

Re: How to count distinct ignoring a single dimension?

Wed, 09 Sep 2015 17:36:59 GMT

Hmmm... perhaps my amount for record #3 is null and yours is zero?

Re: How to count distinct ignoring a single dimension?

sunny_talwar — Wed, 09 Sep 2015 17:39:49 GMT

This is my script:

Table:

LOAD * Inline [

Claim, State, Payment_Type, Amount

1, PA, Expert, 10

1, PA, Loss, 15

1, PA, Legal,

2, PA, Expert, 5

2, PA, Loss, 25

2, PA, Legal, 20

];

So mine is actually null. Also we are doing a Distinct Count of Claim which is not null.

Re: How to count distinct ignoring a single dimension?

Wed, 09 Sep 2015 17:41:21 GMT

Avg(alt(Amount,0)) did not give me the desired answer either. I didn't mention this in the original post, but I do have a few other fields such as injury type which may also be limited.

Re: How to count distinct ignoring a single dimension?

maxgro — Wed, 09 Sep 2015 17:47:35 GMT

and could you post some data with other fields?

Re: How to count distinct ignoring a single dimension?

jonas_rezende — Wed, 09 Sep 2015 17:53:39 GMT

Hi, Justin Bartoszek.

The result was reproduced as its expression. See image below.

Note:The expression count (xxx) acts on the Claim field, so does not interfere with null results. And Sum(Amount) equal null, will be considered zero.

If you want to transform null to zero, make as follows.

Table1:

LOAD Claim, State, Payment_Type, if(isnum(Amount),Amount, 0) as Amount Inline [

Claim, State, Payment_Type, Amount

1, PA, Expert, 10

1, PA, Loss, 15

1, PA, Legal, -

2, PA, Expert, 5

2, PA, Loss, 25

2, PA, Legal, 20

];

I hope this helps!

Re: How to count distinct ignoring a single dimension?

Wed, 09 Sep 2015 18:15:32 GMT

I see what's going on... my table doesn't include a record for each claim and payment type. So if there is no legal amount for claim #1 then there is no record for it. I edited my original post to remove that record from the sample data table.