topic Re: Multiple expressions in same chart in QlikView

Multiple expressions in same chart

Mon, 21 May 2012 12:29:59 GMT

I want to create a chart that has 2 expressions and 2 dimensions. I need 1 of the expressions to calculate for both dimensions and other expression to only consider 1 dimension.

Essentially I am trying to create a chart that shows Market Share (measure 1) of a product for each territory (dimesion 1) over Time (dimeison 2), and I want to show national market share (measure 2) over time. Since its national market share, it should not consider any territory. When I put both expressions in the same chart, the X axis shows both the dimensions instead of just showing the time dimension.

I've attached the sample that I am working on. You'll see 2 charts, I want to be able to merge those into 1.

Thanks for your help!

Multiple expressions in same chart

Jason_Michaelides — Mon, 21 May 2012 12:41:47 GMT

To exclude a dimension from your expression calculations you need to use the TOTAL qualifier. For example, if you have a chart with Region and ProductCategory and an expression Sum(Sales), to exclude the ProductCategory dimension you would use Sum(TOTAL <Region> Sales)

Hope this helps,

Jason

Multiple expressions in same chart

Mon, 21 May 2012 13:32:46 GMT

Thanks Jason! I tried that and few other options but I am not excluding just the data of a dimension but I need this to think that I dont even have a another dimension attribute on the dimension tab.

When I do the TOTAL, the X-axis shows both of the dimensions and the numbers do not make sense with that view.

Thanks!

Re: Multiple expressions in same chart

Jason_Michaelides — Mon, 21 May 2012 14:28:47 GMT

Sorry mate - only thing I can think of is to overlay two charts with a static y-axis max value. See attached.

Re: Multiple expressions in same chart

jonathandienst — Mon, 21 May 2012 14:57:39 GMT

I think with a line chart you can show 1 line with two dimensions (like your first chart), OR you can show multiple expressions. AFAIK you cannot have both.

If the number of territories is fixed and known, you could write one expression per territory and then your second expression.

The other option is to overlay two graphs. This can work but can cause problems when selections are made the the two overlaid charts get out of alignment. Overriding or limiting selections can reduce this problem.

Regards

Jonathan

Re: Multiple expressions in same chart

jonathandienst — Mon, 21 May 2012 15:02:16 GMT

Another suggestion came to mind - create a calculated dimension or an island dimension with the territory name and the final expression name. Then in your expression, write something like (call the dimension TerrDim for sake of example):

=If(TerrDim <> 'National MS%', .... your MS% expression here ...., .... your National MS% expression here)

Load the final expression name last and sort by load order.

Hope that helps

Jonathan

Re: Multiple expressions in same chart

Mon, 21 May 2012 15:57:56 GMT

Can you please eloborate the part about creating calculated dimension? I am not sure I could follow that!

Thanks!

Re: Multiple expressions in same chart

jonathandienst — Mon, 21 May 2012 18:40:51 GMT

Actually I prefer the approach with an logical island dimension - I think it will probably perform better than the calculated dimension and also simpifies the expression. You can create this in script from the data already loaded:

Data:
LOAD Products, 
     Time, 
     Territory, 
     TRX
FROM
(biff, embedded labels);
Island:
LOAD DISTINCT Territory As TerrDim
Resident Data
Order By Territory;
Concatenate (Island)
LOAD 'National MS%' As TerrDim
Autogenerate 1;

Now use TerrDim as the second dimension in the chart and the expression that I described earlier.

Regards

Jonathan

Re: Multiple expressions in same chart

Mon, 21 May 2012 21:18:13 GMT

Hi Jonathan,

I tried your suggestion but i can see either national market share or market share by territory but i cant see both at the same time.I have attached a screen shot which shows how i want to see my final result.dotted line is the national MS%.But i made that using 2 charts by overlapping together but i want to get the same result by using one chart.

Any suggestions?

Thanks in advance