Qlik Community

Qlik Sense Enterprise Documents & Videos

Documents & videos about Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.

Simulating Alternate Dimensions and Measures across multiple visualization objects

hja
Contributor

Simulating Alternate Dimensions and Measures across multiple visualization objects

!One of the great things with some Qlik Sense is the ability to specify Alternative Dimensions and Alternative Measures over default Dimensions and Measures on specific visualizations such as a Bar Chart.  This makes it really easy for a consumer of a published app to perform their form of ad-hoc on a specific visualization without having to go into an EDIT mode.

...but what about syncing an Alternative Dimension or Alternative Measure across several visualization objects at a time?  Or what about some visualization objects that don't offer the Alternative Dimension or Alternative Measure options?

To set this up for your consumers in an app you will publish, a workaround in this case would be to create your own Inline Tables that contain the list of Dimensions or Measures you want to allow for selection as well as creating custom Master Dimension or Master Measures in the App.

As an example, I have a data model that has several fields:

ie.

Dimensions:  Customer, Country, City, ProductName

Measures:  Sales, Expenses, Discount, Gross Profit


I first create Inline Loads within the Data Load Editor to allow for field selection:

//Dimensions

Load * Inline [_dimNo, _dimension

1, Customer

2, Country

3, City

4, Product];

//Measures

LOAD * INLINE [_metricsNo, _metrics

1, Sales

2, Expenses

3, Discount

4, Gross Profit

];

I added the _dimension and _metrics fields to the dashboard page.

I then create a Master Dimension called DimSelect with the following logic:

if (GetSelectedCount([_dimension]) = 1, Pick([_dimNo], Customer, Country, City, ProductName), CategoryName)

The logic specifies that if the Count of the number of dimension values selected within the _dimension field is = 1, pick from a list of which Dimension should be populated into the chart object, else if more than one value is selected or if no value is selected from the _dimension field on the dashboard, default to a specific Dimension field - for me this is CategoryName dimension.

I created a Master Measure called MeasureSelect with similar logic:

if (GetSelectedCount([_metrics]) = 1, Pick([_metricsNo], sum(Sales), sum(Expenses), sum(Discount), sum([Gross Profit])), sum(Sales))

Here, I want to default to Sum of Sales, else, if a specific measure is selected from _metrics field on the dashboard, that is the measure to use within the chart object.

No I just add the DimSelect and MeasureSelect master items to the Chart Objects I want to use.  Cycling through selection in the fields quickly allows cycling of the Alternative Dimension or Measure across charts.

NOTE:  I've noticed that if I select an item in the chart, multiple selections appear in the Global Selector for all values, not a big deal as I can select/un-select/clear, but think it's something to be aware of when using this method.  Most likely it's due to me using the Pick() function but wanted to highlight that the above does work and addresses the use case.  I'll stop here vs. refining further but if you find solutions to this that allows for ease of selection, please share in comments.

Attachments
Comments
neetha_p
Honored Contributor

Hi Harvey,

Great,Thanks for sharing this article.

Regards

Neetha

Not applicable

Hi Harvey, great article, thanks.

Were you able to control the sort order of values within each dimension when picking it from the drop-down list? Nothing I've tried seems to have any effect.

Thanks.

hja
Contributor

I tried it in a Bar Chart but switching the order of the fields to sort on.  Instead of sorting on the Dimension first, I wanted to sort on the values of the measure.  By going into the properties, I've been able to sort by the value of the Measure (Ascending or Descending) just by switching the order of MeasureSelect first and then DimSelect.  Is this what you are looking for?  If not, you may need to turn off 'Auto' sorting and build an expression to create your sort order.

test.png

Not applicable

Harvey, that’s brilliant, thank you…I had tried all sorts of complicated solutions & completely missed this very simple one! Much appreciated.

Not applicable

Could this be applicable with Dates from a master calendar?

graham-crooks
New Contributor III

This is a great solution - I was looking for a way to alternate between Financial and Calendar Year and thought this would be it. However, I've noted a problem... if the dimensions have very different labels, then they update as the dimension is switched - no dramas. If, though, the labels are effectively the same, in my case Month (Jan, Feb, Mar, etc) but refer to different dimensions (Calendar Month, Financial Month), the labels aren't re-ordering when the dimension is switched. Curiously, the data points are re-ordering correctly. I've tried forcing a sort order (pick(SortOrderCode, FinancialMonthNum, CalendarMonthNum)) but this hasn't had the desired effect. Has anyone else observed this issue? Has anyone resolved it and how please?

[I'm using QS3 SR2. Calendar Month 1 = Jan; Financial Month 1 = Apr].

katetsan
New Contributor III

Hi,

I've selected 4 fields from Table A as a cyclic group and select another field called "Tax Type" from the same table A as default dimension. The total amount of default dimension is 4 times of each.

Ex.

Cycle Group: City, Country, Customer and Product

Default Dimension: Tax Type

Total sales amount of (City/Country/Customer/Product)= 100

BUT!! Total sales amount of (Tax Type)=400

Version history
Revision #:
1 of 1
Last update:
‎07-26-2016 06:52 PM
Updated by:
hja