Calculated dimension

[jessica_webb]

Hi,

Hoping there might be an easy solution to this...

I have a 100% stacked bar chart with four expressions.

I would like two dimensions (i.e. two separate bars) - one to show values for all data and one to show filtered values.

In the past I have used something like:

=if(Location=1,'Liverpool', If(Location=2,'Manchester'))   - thereby only including two mutually exclusive categories.

But now I want:

=if(Location=1,'Liverpool', If(Location=ALL,'National'))

Any suggestions greatly appreciated.

Jessica

[Oleg_Troyansky]

Hi Jessica,

it's hard to visualize what kind of a chart you are trying to produce... Can you post a mock-up image from Excel or even a manual drawing of what you are after?

cheers,

Oleg Troyansky

[#3]

Hi Jessica,

If you Need Bars as per the No. of Locations and an Expression that calculates your data(which you have not mentioned),

Then try taking 'Location' as your dimension and create two Expressions , one with the regular calculation and the other with 'Total'. thus you will get seperate Bars for Individual and Total.

Hope it helps !

Cheers!

[jessica_webb]

Hi Oleg,

Of course (probably should have done that to begin with).

I have attached an image of what I would like to eventually produce.

The four sections of each bar are calculated from four different calculated expressions. E.g. 1) % of dog owners, % cat owners, % 'other pet' owners, % 'no pet' owners.

I would like the top bar (pictured) to include all my data (unfiltered by location) and the bottom bar to include only data from a filter (by location).

So, the top bar would show me the percentages of animal owners across the nation (all locations) and the bottom bar would show me the percentages of animal owners in Liverpool only.

Hope this makes things a bit clearer, and huge thanks for your help.

Jessica

[jessica_webb]

Hi P,

Thanks for your response - unfortunately I don't think this will work. I have now included a picture and more detailed explanation of what I need.

Thanks again,

Jessica

[Anonymous]

HI Jessica,

Does National contains Data from Liverpool as well?

If so, I would advise to stack 2 chart objects one on top of the other with one for all and the second one for only Liverpool.

Antoine

[NickHoff]

Hi Jessica,

I had to do something like this in the past.  You need to create an INLINE table for a madeup ID i.e. 1, 2 and then the description of what each calculation is.  The problem is if you have more than 1 dimension you can't have a stacked bar.  So create a new dimension using the INLINE described above..

PatientTrigger:

LOAD * INLINE [

    %PTID, %PatientTrigger

  1, Triggers //(Nameofyourselectionexpression)

  2, Patients //(Nameofallselctionexpression)

];

Then use a PICK statement for your expression ie.

PICK(%PTID,

COUNT(DISTINCT InterventionID),  //expressionforselections

COUNT({$<CACareLine=>}DISTINCT PersonID)) //expressionforallselections

%PatientTrigger(Name of your description column in the inline) will be your new DIM

The PICK is your expressions, so based on the inline the first statement in the PICK will be your ID 1, and second statement will be ID 2.

[NickHoff]

Also your original post stated you had four expressions so your inline wouldn't stop at 2, it would go up to 4, and your pick would have your 4 expressions in it based on the order of the inline table ID.

[rubenmarin]

Hi Jessica, not tested but maybe you can do:

Dimension:

=if(Location=1,'Liverpool','National')

Expressions:

=if(Location=1, YourActualExpression, YourActualExpressionWithTOTALQualifier)

[Oleg_Troyansky]

Hi Jessica,

So if I understand correctly, the first set of 4 bars need to show the "selected" or "available" location, and the second set of bars shows the same of all locations...

So, I'd suggest the following (not tested):

Dimension (calculated):

=ValueList('$(=concat(Location, ', '))', 'ALL')   // This will allow one or more selected locations to be presented in the first bar

Expression:

IF( ValueList('$(=concat(Location, ', '))', 'ALL') = 'ALL',

     sum({1} Value)     // disregard all selections

,

     sum(Value)          // Only selected values

)

Now, to form 4 stacked segments that sum up to 100%, you can either use a second Dimension, or use 4 distinct Expressions.

Hope this one works for you

cheers,

Oleg