Doing multiple compares in if()

Thu, 01 Oct 2015 15:31:00 GMT

Hey everybody,

Sorry for the long winded email but I've banged my head against a wall with this one.

I'm having some trouble doing a countif within a chart. I'm hoping that someone has done something like this before.

I have a dashboard which lays out account(Account_New) and what presence exists by product(Group/Driver).

Basically there are 4 levels of presence that we can have at an account.

Active - The account has an active product

Opportunity - We are negotiating with the account for that product

Lead - We are talking with a contact at an account for a product but we don't have sizing or a prelim contract

White - No presence for that product.

I have one chart which shows the highest level in each Group/Account combination. That works fine.

The problem is with a chart which shows the # breakdown of accounts by Group.

I need a way for the Opp, Lead and White columns to exclude any of the accounts that are included in the previous columns.

Basically if an account has at least 1 active driver then its active for the entire group and should not be counted in the Opp, Lead or White Column.

Same for Opp (Shouldn't been in Lead, White)

Same for Lead (Shouldn't be in White) - This last piece I did solve as I'm doing a Count(Acct) - Count(if(Active, Opp, Lead, Acct))

The # of accounts is the table I need to get working right.

The List of Account Ids shows the account Ids by Group & Level.

I tried the following but it didn't work

1. Created a hidden column in the straight table which concatenates the Active for that BU (List of Account Table)

2. Then tried to add an extra logic to my if - Index(ACTIVELIST, Account_New)=0

My thinking was that it will take the text in the column (ACTIVELIST) and look for the Account_New but its not working.

Any thoughts?

Thanks,

Rob

Re: Doing multiple compares in if()

lironbaram — Thu, 01 Oct 2015 17:06:47 GMT

have a look at the attach example

hope it helps you

Mon, 05 Oct 2015 18:59:14 GMT

Thank you so much. That did the trick.