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
