Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
1st Requirement
Concat(distinct {<Site = , Service = p(Service)>} Site, ', ')
2nd Requirement
Concat(distinct {<Site = , Service = p(Service), SiteType = {'Key'}>} Site, ', ')
1st part of the question:
=Concat({<Site = , Service = p(Service)>} Site)
1st Requirement
Concat(distinct {<Site = , Service = p(Service)>} Site, ', ')
2nd Requirement
Concat(distinct {<Site = , Service = p(Service), SiteType = {'Key'}>} Site, ', ')
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.
I'm an idiot - I figured it out.
Thank you again - this has been SO helpful!!
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)
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
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:
<site selected; 1>
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!
May be use [Associated Site] as a dimension and use Only({<Site = , Service = p(Service)>} Service) as your expression
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?