Bar View: % columns when using filters/splits?

Phillipa · March 22, 2012 7:10AM

I have a simple Bar view which shows the number of doctors prescribing a certain medication per quarter in ranges i.e 1-5 times, 6-10 times etc.)

I can show the number of doctors within each range but had to create a simple formula to show the % of doctors:

1/RECORDCOUNT(SUBSET([Quarter]))

I created 3 different formulas to show % of doctors in institutions, Regions and split by regions and institutions as follows:

1/RECORDCOUNT(SUBSET2([Quarter], [Instituion])),

1/RECORDCOUNT(SUBSET2([Quarter],[Region])),

1/RECORDCOUNT(INTERSECTION( SUBSET([Quarter]), SUBSET([Region]),SUBSET([Instituion])))

These formulas all work if I keep my Bar view static and set my variable to the appropriate formula for the chosen split.

What I would really like is one formula which will calculate the correct percentages when I use filters or change the split of the data between institutions and regions or both. Can anyone help me? I tried aggregating data using the unique ID column and formula result, but that didn't work. Thanks a lot. Phillipa

steve · March 23, 2012 3:39PM

Are you using "Formula" measures in the 2.7 Bar/line view?

Phillipa · March 27, 2012 4:02AM

Dear Steve,

I am using formulas. But as far as I can see static values produce the same results.

What I need is some kind of dynamic formula that can take into account the selections made in split or the filters.

Thanks
Phillipa

steve · March 27, 2012 6:42AM

By "Formula" measures, I mean: in the Measures drop-down of the Bar/line view, tick "Formula" (in italic).

Phillipa · March 27, 2012 9:05AM

I see. Then you would have to reenter a formula into the field.

I tried it with the formula:

1/RECORDCOUNT(INTERSECTION( SUBSET([Quarter]), SUBSET([Region]),SUBSET([Institution])))

but it doesn't like the formula.

Phillipa

steve · March 27, 2012 9:54AM

Try:
1/RECORDCOUNT(SUBSET())

When using formulas for measures in this way, the formula is implicitly evaluated against the subset identified by the bar Split/Pane/etc. options.

I'm not sure if this is what you want. It will evaluate to 1/[the number of records in the given bar].

Phillipa · March 27, 2012 12:22PM

Sorry that doesn't work.

Phillipa

Phillipa · March 27, 2012 12:24PM

I have sent support a file with examples but have no time to rename and protect the data at the moment.

Thanks
Phillipa

steve · March 27, 2012 2:19PM

This formula was used in your file. I opened the measures menu, chose "Formula", and pasted 1/RECORDCOUNT(SUBSET()) ... in fact 1/RECORDCOUNT() should do it, SUBSET() is implicit/redundant.

Phillipa · March 28, 2012 4:28AM

Sorry, but when I paste this formula into formula in the measures menu I get totally wrong numbers.

First I can't show it as percentages and then I get 1s in 3 columnns, which would add upto 300% -the sum of the bars should always add upto 100%.

Am I overlooking something very obvious?

Welcome!

Categories

Ideas Parade

Tagged