topic Re: How to Calculate Expression Values for non-selected dimension in a Listbox? in QlikView

How to Calculate Expression Values for non-selected dimension in a Listbox?

Fri, 20 Nov 2020 18:02:30 GMT

Hi All,

I am stuck at a very unique situation. Tried reading a lot about this but to no avail. Finally decided to post this here, hoping that some one might have faced this issue before.

I have a Listbox, in which the Dimension(which is an Expression), and I have some other metrics being calculated for this specific Dimension. Now as we all know , a property of Listbox is that when we select a value, it gets highlighted in green and all other values are still visible (but are greyed), now here is my problem.

The metrics (other expressions) are not being calculated for non-selected values. Basically, I get values only for the primary dimension not the other metrics. I hope it's clear, I have also added screenshots to tey and explain this better.

Hoping for a quick resolution.

Re: How to Calculate Expression Values for non-selected dimension in a Listbox?

tresB — Fri, 28 Nov 2014 05:33:15 GMT

In the expression you have to include set analysis to disregard selection, and it will probably give you what you want.

PFA

Re: How to Calculate Expression Values for non-selected dimension in a Listbox?

Fri, 28 Nov 2014 06:30:37 GMT

Hi tresesco,

What you have showed is definitely a simpler version of my case. My problem is my expressions are already complex with set analysis included in them (multiple filters). I am not sure how i can add more set analysis into it.

Ex: The field that I take in the listbox is not a dimension, rather an Expression like ;

=If(Aggr(sum({<[Organization ID]=,PlanTrack_R12={'C'},[Sold To Opt Out Flag]={'N'}>}PlanTrack.Sales),[Organization ID])>0,[Organization ID])

Now my metrics are even more complex than these..so not sure about how to proceed. Any ideas would be greatly appreciated.

Re: How to Calculate Expression Values for non-selected dimension in a Listbox?

jyothish8807 — Fri, 28 Nov 2014 06:33:06 GMT

Hi Yogesh,

Can you share your app with the list box. It can be done using set analysis by making your selection ineffective.

Regards

Re: How to Calculate Expression Values for non-selected dimension in a Listbox?

Fri, 28 Nov 2014 06:37:38 GMT

Hi Jyothish,

Unfortunately I can't do that as its placed on a remote server and I can't make a copy of that at this point of time. Hope you can understand.

Re: How to Calculate Expression Values for non-selected dimension in a Listbox?

tresB — Fri, 28 Nov 2014 06:39:18 GMT

In the expression set part you just have to include the selection field, may be like:

Sum({<[Organization ID]=,your existing set analysis>} your exp)

Re: How to Calculate Expression Values for non-selected dimension in a Listbox?

Fri, 28 Nov 2014 06:43:38 GMT

So my Sales metric is something like this:

Num(sum({<PlanTrack_R3={'C'},[Sold To Opt Out Flag]={"N"},PlanTrack.Territory=p($::[Territory Number])>}PlanTrack.Sales),'#,##0.0')

and my ORG ID (which I have taken as the dimension) is defined as something like this:

=If(Aggr(sum({<[Organization ID]=,PlanTrack_R12={'C'},[Sold To Opt Out Flag]={'N'}>}PlanTrack.Sales),[Organization ID])>0,[Organization ID])

So, how would I go about defining the Sale metric to take into consideration my ORG ID?

Re: How to Calculate Expression Values for non-selected dimension in a Listbox?

tresB — Fri, 28 Nov 2014 06:49:48 GMT

Keep the org id as it is, and try other expression like:

Num(sum({<[Organization ID]=,PlanTrack_R3={'C'},[Sold To Opt Out Flag]={"N"},PlanTrack.Territory=p($::[Territory Number])>}PlanTrack.Sales),'#,##0.0')

Re: How to Calculate Expression Values for non-selected dimension in a Listbox?

Fri, 28 Nov 2014 07:24:05 GMT

Thanks Tresesco.

That worked for all my Metrics and they are now being calculated. But I am still stuck with one more column of ORG NAME. The expression for that is something like this:

=If(count({<PlanTrack_R12={'C'},PlanTrack.Sales={">0"},[Sold To Opt Out Flag]={"N"}>}[PlanTrack.MDM ID]) >0, [Organization Name]

& '(' &

count(Distinct{<PlanTrack_R12={'C'},PlanTrack.Sales={">0"},[Sold To Opt Out Flag]={"N"}>}[PlanTrack.MDM ID])

& ')',0)

How can I possibly include the ORG ID filter in this?

Re: How to Calculate Expression Values for non-selected dimension in a Listbox?

jyothish8807 — Fri, 28 Nov 2014 07:26:21 GMT

Hi Yogesh,

Try like this:

Declare the syntax for ORG ID in back end and name it as ORG ID.

eg. if(....your syntax) as ORG ID,

then in front end in your expression for salesmetrix as tresesco mentioned: try lie this:

num(sum({<[ORG ID]=,Plan Track..... your expression>})

Hope it works.

Regards

Re: How to Calculate Expression Values for non-selected dimension in a Listbox?

Fri, 28 Nov 2014 07:31:13 GMT

Actually I am doing a resident load from some other dataset which exists. My constraint is that I should not be defining new variables in the script (I know right!). So is this possible with just expressions?

Re: How to Calculate Expression Values for non-selected dimension in a Listbox?

jyothish8807 — Fri, 28 Nov 2014 07:37:17 GMT

try like this:

if(count({<[Organization ID]=,Plantrack={'C'}......your expression

hope it works.

Regards

Re: How to Calculate Expression Values for non-selected dimension in a Listbox?

Fri, 28 Nov 2014 07:45:01 GMT

I tried what you had mentioned as follows :

=If(count({<[Organization ID]=,PlanTrack_R12={'C'},PlanTrack.Sales={">0"},[Sold To Opt Out Flag]={"N"}>}[PlanTrack.MDM ID]) >0, [Organization Name]

& '(' &

count(Distinct{<[Organization ID]=,PlanTrack_R12={'C'},PlanTrack.Sales={">0"},[Sold To Opt Out Flag]={"N"}>}[PlanTrack.MDM ID])

& ')',0)

Now the problem is only the second part of the expression is working (so I am only getting the count like (10) (25) etc), 1st part isn't working!