How to count distinct ignoring a single dimension?

[#1]

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.

[sunny_talwar]

I see 10 for legal:

[maxgro]

try this

Avg(alt(Amount,0))

or

sum(Amount) / Count(Claim)

[#4]

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

[sunny_talwar]

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.

[#6]

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. 

[maxgro]

and could you post some data with other fields?

[jonas_rezende]

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!

[#9 · Accepted solution]

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.