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

[jessica_webb]

Hi Antoine,

National does also contain data from Liverpool.

I have currently got 2 chart objects one on top of the other, but as I need the caption bar at the top, it looks a bit disjointed, so I'm hoping that there is a way to get both bars in one chart!

Thanks for your advice,

Jessica

[jessica_webb]

Hi Ruben,

Unless I've misunderstood, your solution would give me a bar with just Liverpool data, and a bar with all other data (National). Unfortunately, I need the National bar to include all data (inc. Liverpool data).

Thanks for your advice,

Jessica

[jessica_webb]

Hi Oleg,

This gives me the right 'look' (I end up with two stacked bars with four sections, one labelled 'ALL' and one labelled 'Liverpool' (when selected)) so it's a great suggestion.

Unfortunately, both bars are pulling in the exact same data for some reason so the Liverpool bar, despite being labelled as Liverpool, is actually showing All data.

Thanks for the suggestion though,

Jessica

[rubenmarin · Accepted solution]

Hi Jessica, I tested it:

From this data:

Data:

LOAD * Inline [

Location, Sales

1, 100

2, 200

];

Returns:

Using the TOTAL qualifier, Liverpool is included in National, the expression is:

=if(Location=1, Sum(Sales), Sum(TOTAL Sales))

[jessica_webb]

Hi Ruben,

My apologies - I didn't think that would give me the right result (should have known better!).

Would you be able to help me rewrite my expressions? All four are the same except that 'Pet Category' changes in each case.

=COUNT ( {1<[Pet Category]={4},[Age range]={'Mid'}, [Date set]={1} >}

DISTINCT [RespondentID])/COUNT (

{1<[Pet Category]={1,2,3,4}, [Age Range]={'Mid'}, [Date set]={1} >}

DISTINCT [RespondentID])

So far, based on your advice I've got:

=IF (Location='Liverpool', COUNT ( {1<[Pet Category]={4},[Age range]={'Mid'}, [Date set]={1} >}

DISTINCT [RespondentID])/COUNT (

{1<[Pet Category]={1,2,3,4}, [Age Range]={'Mid'}, [Date set]={1} >}

DISTINCT [RespondentID]), ........)

But I'm not sure how to rewrite the expression with a TOTAL qualifier.

Thank you so much,

Jessica

[jessica_webb]

Hi Nick,

Thanks for your advice.

I have created the INLINE as:

Ownership:

LOAD * INLINE [

    %ID, %Ownership

  1, Dog

  2, Cat

3, Other

4, None

];

But I'm lost on the expression part.

Do I need to add the PICK statement to all four expressions?

At the moment, each expression is as follows (with just 'Pet Category' changing in each):

=COUNT ( {1<[Pet Category]={4},[Age range]={'Mid'}, [Date set]={1} >}

DISTINCT [RespondentID])/COUNT (

{1<[Pet Category]={1,2,3,4}, [Age Range]={'Mid'}, [Date set]={1} >}

DISTINCT [RespondentID])

Thanks once again,

Jessica

[rubenmarin]

Ok, not tested (this will be hard to test without sample document) but maybe:

=IF (Location='Liverpool', COUNT ( {1<[Pet Category]={4},[Age range]={'Mid'}, [Date set]={1} >}

DISTINCT [RespondentID])/COUNT (

{1<[Pet Category]={1,2,3,4}, [Age Range]={'Mid'}, [Date set]={1} >}

DISTINCT [RespondentID]),

COUNT (TOTAL {1<[Pet Category]={4},[Age range]={'Mid'}, [Date set]={1} >}

DISTINCT [RespondentID])/COUNT (TOTAL

{1<[Pet Category]={1,2,3,4}, [Age Range]={'Mid'}, [Date set]={1} >}

DISTINCT [RespondentID])

)

[jessica_webb]

That's fantastic! Thank you so much Ruben, works perfectly.

Very much appreciated!

Jessica

[Anonymous]

Brilliant !!!!

[NickHoff]

PICK(%ID,

COUNT ( {1<[Pet Category]={1},[Age range]={'Mid'}, [Date set]={1} >}DISTINCT [RespondentID])/COUNT (

{1<[Pet Category]={1}, [Age Range]={'Mid'}, [Date set]={1} >}DISTINCT [RespondentID]),

COUNT ( {1<[Pet Category]={2},[Age range]={'Mid'}, [Date set]={1} >}DISTINCT [RespondentID])/COUNT (

{1<[Pet Category]={2}, [Age Range]={'Mid'}, [Date set]={1} >}DISTINCT [RespondentID]),

COUNT ( {1<[Pet Category]={3},[Age range]={'Mid'}, [Date set]={1} >}DISTINCT [RespondentID])/COUNT (

{1<[Pet Category]={3}, [Age Range]={'Mid'}, [Date set]={1} >}DISTINCT [RespondentID]),

COUNT ( {1<[Pet Category]={4},[Age range]={'Mid'}, [Date set]={1} >}DISTINCT [RespondentID])/COUNT (

{1<[Pet Category]={4}, [Age Range]={'Mid'}, [Date set]={1} >}DISTINCT [RespondentID])))))