topic Re: Change chart expression based on selection in QlikView

Change chart expression based on selection

Hoowling — Wed, 25 Nov 2020 16:16:04 GMT

Hi community!

I would like alter the chart expression based on the list box selection made.
The expression I would like to be dynamic is:

=Avg({<[Info Bransch]=P({$} [Info Bransch])>}[EBIT-marg Senaste])

Where [EBIT-marg Senaste] should change given my selection in the list box (EBIT-marg Senaste is a dimension).

THankful for any help!

Re: Change chart expression based on selection

chrismarlow — Sat, 16 Feb 2019 11:58:52 GMT

Hello again,

In this case the set analysis/P() seems redundant, you use that where you need to do something more complex based on selections on another field or having 'cancelled' selections on another field (which is what you other thread was doing).

Curious that EBIT-marg Senaste is both a dimension and a measure (since you are taking an average) - although is possible in Qlik.

May be easier with a picture of what you would like to happen & a small chunk of sample data.

Cheers,

Chris.

Re: Change chart expression based on selection

Hoowling — Sat, 16 Feb 2019 12:17:05 GMT

The P()function is used in this case to generate the average EBIT-margin for all the peers of the selected company, i.e. companies with the same "info bransch" as the selected company.

In the below picture, I would like to change the values of the chart table (red arrow) based on the list box selection (red circle). I've also pasted a picture of my data.

Re: Change chart expression based on selection

Hoowling — Sat, 16 Feb 2019 12:18:06 GMT

Re: Change chart expression based on selection

chrismarlow — Sat, 16 Feb 2019 12:36:59 GMT

Hi,

Ok. You still need to cancel the selection on company in the set to get the peers, I think looking at your spreadsheet Company is Bolagsnamn, so;

Avg({<Bolagsnamn=,[Info Bransch]=P({$} [Info Bransch])>}[EBIT-marg Senaste])

But looking at your dashboard I think you also need this to be looking at a specific date & the other objects have time series? In which case I think that needs added to the set, depending on how your time series data works.

Cheers,

Chris.

Re: Change chart expression based on selection

Hoowling — Sat, 16 Feb 2019 13:01:39 GMT

OK, I've included Bolagsnamn in the set.

The time series can be ignored since this data is gathered from another source and I only have time series data for a very limited number of companies.

How would you go about making the expression dynamic i.e. changing "EBIT-marg Senaste" to the list box selection?

Regards,
Philip

Re: Change chart expression based on selection

chrismarlow — Sat, 16 Feb 2019 16:03:38 GMT

Hi Philip,

What field does that list box look at?

I am slightly lost with your data model. If the EBIT-marg Senaste etc exists both as a member of a field, and the name of a field then you could use a list box on the field it is a member of, to then make a selection and then use a dollar sign expansion to use that in the aggregation expression, so something similar to;

But there are other ways of populating the list box that mean that is not required in the data model & this approach would not work.

Cheers,

Chris.

Re: Change chart expression based on selection

Hoowling — Sat, 16 Feb 2019 20:54:25 GMT

Quick question before sharing an example file - isn't it possible to replace the "EBIT-marg Senast" text string with a variable? Where the text string generated by the variable changes with a list box selection.

Cheers,
Philip

Re: Change chart expression based on selection

chrismarlow — Sun, 17 Feb 2019 19:33:02 GMT

Hi,

Yes, that is probably where this is going, it is how the variable and the list box interact that is what to work out.

Cheers,

Chris.

Re: Change chart expression based on selection

Hoowling — Wed, 20 Feb 2019 06:21:09 GMT

Hi,

I've created a variable (varLönsamhet) that generates the text string I want to replace "EBIT-marg Senaste" with in below statement:

=Avg({<[Bolagsnamn]=,[Info Bransch]=P({$} [Info Bransch])>}[EBIT-marg Senaste])

How do I incorporate the variable in this expression?

FYI - varLönsamhet = GetFieldSelections(Nyckeltal,true(),true(), 'Lönsamhet')

Re: Change chart expression based on selection

chrismarlow — Wed, 20 Feb 2019 18:56:17 GMT

Hi,

Should be;

=Avg({<[Bolagsnamn]=,[Info Bransch]=P({$} [Info Bransch])>}[$(=varLönsamhet)])

Cheers,

Chris.

Re: Change chart expression based on selection

Hoowling — Fri, 22 Feb 2019 06:03:53 GMT

Hi,

Something is not working properly with that expression:
(ps. changed field name from bolagsnamn to bolag).

Re: Change chart expression based on selection

chrismarlow — Tue, 05 Mar 2019 19:47:32 GMT

Hi,

I don't understand how that variable works. If you put it in a text box does it show the value you want?

Cheers,

Chris.