Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Multiple expressions in same chart

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!

8 Replies
jason_michaelid
Honored Contributor II

Multiple expressions in same chart

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

Not applicable

Multiple expressions in same chart

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!

jason_michaelid
Honored Contributor II

Re: Multiple expressions in same chart

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

MVP
MVP

Re: Multiple expressions in same chart

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Re: Multiple expressions in same chart

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Multiple expressions in same chart

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

Thanks!

MVP
MVP

Re: Multiple expressions in same chart

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Multiple expressions in same chart

QV Image.bmp 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

Community Browser