Prize goes to the person that could figure this out

[#1]

I have an interesting question. We utilize scorecards quite extensively here that look at recent weekly activity as well as prior month, prior year, and plan numbers.

We also run comparisons against these numbers. In other words we compare

current week to prior week

current month to prior month

current month to plan

current month to same month prior year.

This can be put into a spreadsheet quite easily. It is much more cumbersome to put this on a dashboard without alot of custom coding. I've checked as several demo apps but none are able to hand our particular scenario without a ton of custom work.

Below is a series of charts that I've put together to appear as one chart. Although I'm only showing a metric called orders, this chart will have about 7 more metrics. Also...I'm showing orders for one partner. This composite chart will be created for the current top 20 partners. These partners will change often.

Copying these charts, then aligning them to be exactly next to each other and changing the formula to reflect a new partner (I'm using set analysis to limit this to the particular partner) is looking to be just too much manual coding.

I changed my data structure to make this into one chart instead of 20 charts. It works great. I however have a new problem. Some of the values are supposed to be integers and some are supposed to be formated as percentages. In the chart below, the column labelled Variance 1/16 to 1/23 is supposed to be a %)

Does anyone know of a way to conditionally format a cell based on a flag that I create?

for example

if the flag = 1 then format as num(field,'#,####')

if the flag = 2 then format as num(field,'#,####%')

[rwunderlich · Accepted solution]

There are a number of ways, Here is one suggestion.

=num( sum(myfield), pick(flag, ,'#,####', '#,####%')

-Rob

[#3]

That did it!!!

Thanks for the help