Dynamically choosing dimensions for graphs / tables

[jacqueshol]

Hi there,

I have noticed in one of the Qlik Sense demos (https://sense-demo.qlik.com/sso/sense/app/0be2146a-8ea0-4aca-8853-200f957f7307) on the 4th tab (Details) that it is possible to dynamically select dimensions for graphs / tables.

I have a simple bar chart showing the sum(Sales).  However, I want to be able to dynamically select whether I want to see the graph by weekday or sales rep by clicking on the relevant dimension in a list box (?) on the page.  The demo also allows you to select which measure to display in the graph. It would appear to me that the available options for dimensions and measures are presented in two different list boxes.

So, my questions are:

1. How do I create a list of available dimensions (and measures) as it was done in the demo?
2. How do I create such a graph / table to be able to choose which dimension I want to use on my x-axis?

I have been searching the internet, but haven't found a solution yet.  Can anyone please point me in the right direction or even better to a link that explains the above in a bit more detail?

Thank you,

Jacques

[Matthew_Grayndler]

The specific functionality used in this demo app is the Climber Custom Report extension, as detailed on the Qlik Design Blog here:

http://https://community.qlik.com/blogs/qlikviewdesignblog/2017/05/05/custom-report-extension

petter-s‌ has detailed how to achieve similar capability without the use of extensions.

[petter]

This will not work well:

MEASURES:

LOAD * Inline [

Measures

Avg([Time Out])-Avg([Time In])

sum(StagNo)/count(Distinct Date)

];

As the red square brackets come in conflict with the blue ones and confuse the Qlik load script interpreter. So you can rather write this:

MEASURES:

LOAD * Inline "

Measures

Avg([Time Out])-Avg([Time In])

sum(StagNo)/count(Distinct Date)

";

Or the other way around ... then it will work.

[jacqueshol]

Hi Petter,

Thank you kindly, that work perfectly. I know I promised that my last question would've been my last, but I have one more if you don't mind .

I've loaded the following at the end of my script to import the measures to be used in a filter pane:

MEASURES:

LOAD * Inline "

Measures

count({<PlanActual={'Actual'}>} RecNo)/count({<PlanActual={'Actual'}>} Distinct Date)

";

However, when I create a filter pane with the Measures, my options in the filter pane is exactly as above, i.e.

count({<PlanActual={'Actual'}>} RecNo)/count({<PlanActual={'Actual'}>} Distinct Date)

Is it possible to label these, e.g.

count({<PlanActual={'Actual'}>} RecNo)/count({<PlanActual={'Actual'}>} Distinct Date) AS Receipts per Day

I want to make the labels more descriptive in the filter pane, but obviously need to keep the formulas. 

Thanks again,

Jacques

[petter]

Sure - have two columns instead of one in your table like this:

MEASURES:

LOAD * INLINE "

Measure,Expression

Total Sales,Sum([Sales])

Average Sales,Avg(Sales)

Most common Sales,Mode(Sales)

";

Then use Measure in the list box or filter pane and use Expression in the measures expression. Then Qlik's associative model will take care of the rest.

[jacqueshol]

Hi Petter,

I have learned so much from you over the past day and I really don't know how to thank you, but I am really grateful for all your assistance.

Thanks again

Jacques

[petter]

You're welcome , I am happy to help.

[petter]

A small additional note on the quoting characters you can use in an LOAD ... INLINE:

You can use either [ and ]  or " and " or the back-tick character ` and `. The last option might be the safest overall if you want to avoid the conflict within the "data section" of you INLINE load statement....

This is a very good article about the subject and it is valid for Qlik Sense also even though it was written for QlikView:

QlikView Quoteology

[jacqueshol]

‌Thanks Peter, that quoteology article really made a few things clear. I wasn't aware that one could use the back tick character too.

[gvalenti]

I Petter, actually it does not work in the Scatter Plot using the same dimension that you show in the example, any ideas to avoid the problem??

[wonkymeister]

great soloution ref the selection box, adding dimension names via an Inline Load. 1 daft question if i may though?

if i have 5 dimensions to select from to display in my table - how do i ensure only 1 dimension can be selected at a time?