Problems With Cross Table Data Access

[#1]

Hi

I think I have outlined my problem with this example. I am sure there is some set analysis or a function that could help me. If somebody could solve my problem I would be very thankful.

[#2]

Hi Jon, can you post the target results?

Regards

[johnw]

Can you explain what numbers you DO want in your example? And how they should be calculated?

Because just spot checking, things look right to me in the first chart that you say is wrong. Take Group A, Subgroup Zeta. I see two members, afagsdfaa and George. Each has a claim, 2 for afagsdfaa, and 8 for George. Claim 2 cost 300. Claim 8 cost 340. The total cost of claims for members of this group and subgroup is 640, which is what the chart shows. The average cost per member is thus 640 / 2 = 320, which could be done by simple division in your original chart.

Next group and subgroup, A Alpha. There are five members in this group. Between them they have three claims, 1, 7 and 15. The total cost of those claims is 200 + 400 + 100 = 700. That's what the chart shows. The average cost, per member, of claims would be 700 / 5 = 140. Again, simple division.

I'm not going to keep going through ALL of these one by one. If I'm calculating it wrong, please tell me how it SHOULD be calculated. If I'm calculating it right, but group C subgroup Zeta is wrong, please TELL me instead of having me manually calculate everything.

(Edit: Sorry, didn't see previous reply before posting this because I had this sitting on a separate tab for a few hours before I got around to it. But yeah, we both seem to be having the same problem.)

[#4]

Hi John,

FIrst of thanks for looking at my issue and I am sorry I did not explain it fully. I have updated my example to show what my desired result would have and I removed some fields that may have been confusing. The problem is when I use GroupID and SubgroupID as demensions I can get the Member count that I want but not the Cost that I want. When I use the GroupClmID and the SubgroupClmID I get the cost I want but not the member count I want.

In my sample data set I on purpose set it up to have these issues because that is what we have in our much larger production enviroment. The way we have the data mapped in production can not be changed so I am trying to find a creative work around. I am hoping to find a generic fix to this because there are a lot of charts I will have to go and update.

Again thanks for looking into my issue.

[johnw · Accepted solution]

The expected numbers are good, but it would also help if you'd say HOW you're calculating the numbers. But maybe it's obvious. Going through your list.

A Alpha, three claims, cost 700, five members, 700 / 5 = 140. Check.
A Beta, two claims, cost 840, three members, 840 / 3 = 280. Check.
A Zeta, one claim, cost 340, three members, 340 / 3 = 113. Check.
B Alpha, one claim, cost 400, two members, 400 / 2 = 200. Check.
B Beta, no claims, cost 0, three members, 0 / 3 = 0. Check.
B Zeta, three claims, cost 1300, five members, 1300 / 5 = 260. BAD.
C Alpha, two claims, cost 700, four members, 700 / 4 = 175. Check.
C Beta, two claims, cost 900, three members, 900 / 3 = 300. Check.
C Zeta, one claim, cost 600, two members, 600 / 2 = 300. Check.

OK, so did you just mess up the calculation for B Zeta? Did I? Still looks like 1300 across five members to me. If we had four members, that would give us 325. And one of our five members doesn't have a claim. But if we removed members with no claims, that would mess up a bunch of the other numbers, so that can't be it.

Well, here's something that looks funny with B Zeta - Member asdgaasdf has claim 6, but that is a duplicate of the claim by Frank in B Alpha. Do we have any other duplicate claims? Yes, several. I guess that's a standard practice. It appears that the claims are included only in their group and subgroup, and the members only in their group and subgroup, an it's OK for a claim to be for a different group and subgroup than the member.

And I guess that explains why you're trying to keep these tables separate - the group and subgroup of a Member should NOT lock us in to a group and subgroup of the claim, and vice versa. Makes sense, even if I still can't duplicate the 325 number.

I'll just assume the 325 was a mistake, and that it's supposed to be 260.

If so, then you can do it just like I did it in my head. I went through each of the possible values for the group and the subgroup, and compared them to each chart in turn to get the cost and number of members. In other words, create a master list of the groups and subgroups, disconnected from your data. Use count(if()) and sum(if()) to connect the master list to your actual data in the chart.

Master:
LOAD DISTINCT
GroupID as MasterGroupID
,SubgroupID as MasterSubgroupID
RESIDENT Membership
;
OUTER JOIN (Master)
LOAD DISTINCT
GroupClmID as MasterGroupID
,SubgroupClmID as MasterSubgroupID
RESIDENT Claims
;

Chart:
dimension 1 = MasterGroupID
dimension 2 = MasterSubgroupID
Cost = sum(if(GroupClmID=MasterGroupID and SubgroupClmID=MasterSubgroupID,Cost))
Member = count(distinct if(GroupID=MasterGroupID and SubgroupID=MasterSubgroupID,Member))
Cost/Member = Cost/Member

I don't LIKE that approach, as "island data" like this comes with a bunch of problems, and I think there should be a way to join all of the data together naturally so that the correct answers simply appear, but the answer isn't jumping out at me, so I thought I'd at least post what I have. A poor solution is better than no solution.

[#6]

Thanks so much John! Yes I did put the wrong number there. I do believe this is the type of fix I am looking for. I also agree that a proper join may be a better fix but this is still a decent temporary solution.