Synthetic Dimension

[Bob07]

Hi everyone, 

Please I need your help. I have a field called Region with values Asia, EMEA, Latin America & North America. I am showing the sales for each region over time on a line chart. Is it possible to create a 5th line that shows the total of all sales as "CompanyTotal" on the line chart? A sample of my data and line chart is shown below. 

[Chanty4u]

You can use value list 

=ValueList('Asia', 'EMEA', 'Latin America', 'North America', 'CompanyTotal')

And use measure  pick match for your expression 

Then 

Plot one line for each region.

Add a 5th line for the total across all regions (CompanyTotal).

[Bob07]

Thank you @Chanty4u. Can you explain how the measure bit works?

[stevedark]

Hi,

I would do this in the data model by loading a new table that has all the dimension values in it for Region and then a second column Region With Total. Each region would link to itself and also to Total. This can be done in an INLINE load, or a RESIDENT load:

RegionWithTotal:
LOAD
   Region,[Region With Total]
INLINE [
Region,Region With Total
Asia,Asia
Asia,Total
EMEA,EMEA
EMEA,Total
];

You can then simply use the Region With Total field as your dimension.

I've done a blog post on this technique, and various other uses of the same approach:
https://www.quickintelligence.co.uk/qlikview-accumulate-values/

Hope that helps,

Steve