8 Replies Latest reply on Nov 19, 2013 10:16 AM by Michelle Eich

# calculated field with string output based upon a row attribute and a column attribute

Hi there,

I originally posted a question here:  calculated field with string output along customer per customer segment The calculated field provided by Jonathan Drummey was:

IF TOTAL(SUM([Number of Records])) > 0 THEN

IF ISNULL(LOOKUP(SUM([Number of Records]),0)) THEN 1

ELSE

0

END

END

It worked as I had hoped and was very excited but then I learned I didn't fully understand the request of my user.    This question plays off of that request but goes a step further.

I've attached a dummy workbook in hopes of finding the help I need.

I have Agent, Company and LOB(Line of business) in my data set.

If an agent has at least 1 record with a specific company and LOB, it should show as E - Existing.   But now I want to determine those relationships that are available.

Notice those with any num of records in the "currently" view change to E's in the "desired output" view.  Again simple enough.

However, an LOB for a particular company can only be marked as Available if there exists an E somewhere in that company row AND exists an E somewhere in that LOB column. Meaning the agent already does business in that LOB with another Company AND already does business with that Company.  Have I confused you? I'm pulling my hair out on this one.

So because Jeff does 123c (LOB) business already AND  has a relationship with company GHI already, then LOB 123c should be marked as AVAILABLE for Company GHI.

 Currently  (num of records) Desired Output LOB LOB Agent Company 123a 123c 456b 789c Agent Company 123a 123c 456b 789c Jeff ABC 1 Jeff ABC E A GHI 1 GHI A E Paul ABC 8 Paul ABC E A DEF 4 DEF E A GHI 1 GHI A E
• ###### 1. Re: calculated field with string output based upon a row attribute and a column attribute

hmm, maybe something like this...  Just showing the Alpha data to make it cleaner to read.

• ###### 2. Re: calculated field with string output based upon a row attribute and a column attribute

Wow! Ah, this definitely looks like it! However even when I duplicate your sheet I can’t replicate your exact view. Is there a method to the ordering of when and where you pull over the measures?

• ###### 3. Re: Re: calculated field with string output based upon a row attribute and a column attribute

Michelle,

Here, hopefully this will help explain.  I also made it a little simpler, dropped the running_sum.    Each shaded band has the calculations needed to get down to the status.  For each calc that has a triangle(delta)... you'll need to right click, hit edit table calc, copy the formula and use the same "compute using" as well.

Basically I counted the LOB's the Agent had done. Then I used a "total" table calc function, computed using a partition of Agent & LOB. (flags the LOB the Agent had any records for, down the pane for all the Agent's Companies)

Next, we used same tactic, counting the Companies the Agent had done business with.  A "total" table calc function is used, computed using a partition of Agent & Company.  (flags all the LOB's across the pane, for each Company the Agent had any records for)

Finally, I used  a little logic to compare the flags and figure if it was Existing, Available, or nada.

Cheers,

Mark

1 of 1 people found this helpful
• ###### 4. Re: calculated field with string output based upon a row attribute and a column attribute

Thanks so much Mark. I was able to correct the issues I was having with duplicating your view by addressing the “compute using.”

It does exactly as I had hoped.  Thanks!

But once I saw the view with my real data I realized I had missed a huge consideration.  Even if an agent has an existing relationship with the Company and does business with another company for that same LOB to what currently shows as available; it is only truly available if that Company actually offers that LOB (which is determined by seeing if that Company has that LOB with another agent.)   Any thoughts how to add this new consideration into the mix?

• ###### 5. Re: calculated field with string output based upon a row attribute and a column attribute

I believe I need a Company_has_LOB flag. However for the life of me I can't seem to get the "compute using" to work correctly.

• ###### 6. Re: calculated field with string output based upon a row attribute and a column attribute

Yep, that's how I would attack it, Michelle.  In the compute using, choose advanced.  You'll get 2 panes...partitioning and addressing.  Partitioning kind sets the Grouping and Addressing tells you attribute to compute across.

Lets see, we'll want to set a flag for the LOBs the Company had any record for, down all Agents of that Company.  So our partition should prolly be Company & LOB with the addressing as Agent.  Modifying our IF Then for the new calc, that should give you a 1 for each Agent and LOB where a Company is already involved in that LOB.

But I'll try to test it out here in a bit.

Cheers,

Mark

• ###### 7. Re: Re: calculated field with string output based upon a row attribute and a column attribute

Well, the business rules are starting to get complicated, but I think it works, lol.

Cheers,

Mark

1 of 1 people found this helpful
• ###### 8. Re: calculated field with string output based upon a row attribute and a column attribute

Thanks Mark. This is exactly it. I appreciate your continued help and sticking with my ever growing business requirements.

I find the "compute using" a bit difficult to master.   I'm trying to add in another text column to the crosstab, just an account number field so end user doesn't have to look elsewhere. But it drastically screws up my calculations and I can't seem to figure it out.  The saga continues...