topic Re: Indirect Dimension Selection in App Development

Indirect Dimension Selection

joey_lutes — Tue, 02 Aug 2016 16:18:17 GMT

I'm in the process of building a dashboard report that works nearly only with dimensions in Qlik Sense 2.2.3.

Through the process, I need the ability to display indirect selections within a straight table of data. Here is simplified example:

Service Site SiteType

A 1 Aux

A 3 Key

B 2 Aux

B 3 Key

C 1 Aux

C 6 Aux

C 7 Key

Suffice to say that Service A is available at Sites 1 and 3 etc.

My intent would be to have a user select a (multiple) Site - Site 1.

As Site one is selected, possible services are A and C.

I need to show that Indirectly, Sites 1, 3, 6 and 7 are tied to those services.

Furthermore, for services A and C, I want to identify the Key site associated with each (in this case, sites 3 and 7 respectively).

I believe from what I've gleaned from Indirect Record Selection

that this is possible using {p}, though my attempts thus far have failed.

Thoughts/ideas?

Thank you

Re: Indirect Dimension Selection

sunny_talwar — Tue, 02 Aug 2016 16:39:03 GMT

1st part of the question:

=Concat({<Site = , Service = p(Service)>} Site)

Re: Indirect Dimension Selection

sunny_talwar — Tue, 02 Aug 2016 16:44:22 GMT

1st Requirement

Concat(distinct {<Site = , Service = p(Service)>} Site, ', ')

2nd Requirement

Concat(distinct {<Site = , Service = p(Service), SiteType = {'Key'}>} Site, ', ')

Re: Indirect Dimension Selection

joey_lutes — Wed, 03 Aug 2016 20:51:39 GMT

This is fantastic, Sunny, THANK YOU! I'm still playing with iterations of it.

So, how would I add a count to them (in a separate KPI or something) I tried wrapping the Concat statement in a Count, but it hates that.

So for example - in the first requirement, the count would be 5.

Thanks again.

Re: Indirect Dimension Selection

joey_lutes — Wed, 03 Aug 2016 21:41:35 GMT

I'm an idiot - I figured it out.

Thank you again - this has been SO helpful!!

Re: Indirect Dimension Selection

sunny_talwar — Wed, 03 Aug 2016 21:53:21 GMT

All you need is to replace Concat with Count and remove the comma separator at the end to get the count

1st Requirement

Count(distinct {<Site = , Service = p(Service)>} Site)

2nd Requirement

Count(distinct {<Site = , Service = p(Service), SiteType = {'Key'}>} Site)

Re: Indirect Dimension Selection

sunny_talwar — Wed, 03 Aug 2016 21:58:03 GMT

No problem at all

Please close this thread by marking the correct answer if you got what you were looking for.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

Re: Indirect Dimension Selection

joey_lutes — Thu, 04 Aug 2016 17:17:29 GMT

Hi Sunny,

One more question for you (I think/hope?!)

Let's say I wanted to list these individually in a table - not concatenated via ', '

So essentially the result would look like:

Service Associated Site

A 1

A 3

C 1

C 6

C 7

Would the formula be different, or is there a different strategy that would be used?

Thank you!

Re: Indirect Dimension Selection

sunny_talwar — Thu, 04 Aug 2016 17:20:22 GMT

May be use [Associated Site] as a dimension and use Only({<Site = , Service = p(Service)>} Service) as your expression

Re: Indirect Dimension Selection

joey_lutes — Thu, 04 Aug 2016 17:59:49 GMT

As [Associated site] is derived from a calculated expression, and I seem to be unable to add an expression as a dimension, that's returning 'Invalid Dimension'

Thoughts?

Re: Indirect Dimension Selection

sunny_talwar — Thu, 04 Aug 2016 18:15:00 GMT

I guess add Site as your dimension and this as your expression -> Only({<Site = , Service = p(Service)>} Service)

Re: Indirect Dimension Selection

sunny_talwar — Thu, 04 Aug 2016 19:07:43 GMT

My bad, you would need to add Site and Service as dimensions and may be use this as your expression-> Only({<Site = , Service = p(Service)>} SiteType)