topic Re: Calculated dimension in QlikView

Calculated dimension

jessica_webb — Tue, 10 Feb 2015 15:41:40 GMT

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

Re: Calculated dimension

Oleg_Troyansky — Tue, 10 Feb 2015 15:49:14 GMT

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

Re: Calculated dimension

Tue, 10 Feb 2015 15:51:08 GMT

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!

Re: Calculated dimension

jessica_webb — Tue, 10 Feb 2015 15:58:35 GMT

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

Re: Calculated dimension

jessica_webb — Tue, 10 Feb 2015 15:59:59 GMT

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

Re: Calculated dimension

Anonymous — Tue, 10 Feb 2015 16:11:51 GMT

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

Re: Calculated dimension

NickHoff — Tue, 10 Feb 2015 16:11:57 GMT

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.

Re: Calculated dimension

NickHoff — Tue, 10 Feb 2015 16:27:47 GMT

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.

Re: Calculated dimension

rubenmarin — Tue, 10 Feb 2015 16:48:51 GMT

Hi Jessica, not tested but maybe you can do:

Dimension:

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

Expressions:

=if(Location=1, YourActualExpression, YourActualExpressionWithTOTALQualifier)

Re: Calculated dimension

Oleg_Troyansky — Tue, 10 Feb 2015 23:17:02 GMT

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

Re: Calculated dimension

jessica_webb — Wed, 11 Feb 2015 12:35:05 GMT

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

Re: Calculated dimension

jessica_webb — Wed, 11 Feb 2015 12:37:56 GMT

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

Re: Calculated dimension

jessica_webb — Wed, 11 Feb 2015 12:45:01 GMT

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

Re: Calculated dimension

rubenmarin — Wed, 11 Feb 2015 12:46:38 GMT

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))

Re: Calculated dimension

jessica_webb — Wed, 11 Feb 2015 12:59:50 GMT

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

Re: Calculated dimension

jessica_webb — Wed, 11 Feb 2015 13:03:30 GMT

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

Re: Calculated dimension

rubenmarin — Wed, 11 Feb 2015 14:21:25 GMT

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])

)

Re: Calculated dimension

jessica_webb — Wed, 11 Feb 2015 14:59:38 GMT

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

Very much appreciated!

Jessica

Re: Calculated dimension

Anonymous — Wed, 11 Feb 2015 15:04:06 GMT

Brilliant !!!!

Re: Calculated dimension

NickHoff — Wed, 11 Feb 2015 15:06:43 GMT

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])))))