Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
sdealtrey
New Contributor

Formula to select specific dimensions

Hello,

I am looking for a formula that will automatically segregate the dimension values I need. I shared a screenshot of the below to help explain what I am looking for.  The dimension is the TECH_ID1 field and I am looking to only have those that start

with "M" followed by four digits. Like "M0101" (shown below). Since each tech Id has a different set of four unique digits, the only common letter is the M at the prefix. Unfortunately the data set is much bigger than below so it would take some time to select all the right ID's. Is there a way to only select the ID's that start with M?   Thanks!



Screen Shot 2018-04-20 at 3.40.45 PM.png

1 Solution

Accepted Solutions
MVP
MVP

Re: Formula to select specific dimensions

You can use a search in a set expression:

Sum( {<RowID={"=(TECH_ID1 Like 'M*') AND Len(KeepChar(Mid(TECH_ID1,2,4),'0123456789'))=4"}>} [Mechanical Sales] )

All the measures have to have the same Set Expression

For this search to work reliably you have to have a row id. You can create one if you dont already have one by using in the LOAD statement this:

LOAD

  RowNo() AS RowID,

  .....

  .....

1 Reply
MVP
MVP

Re: Formula to select specific dimensions

You can use a search in a set expression:

Sum( {<RowID={"=(TECH_ID1 Like 'M*') AND Len(KeepChar(Mid(TECH_ID1,2,4),'0123456789'))=4"}>} [Mechanical Sales] )

All the measures have to have the same Set Expression

For this search to work reliably you have to have a row id. You can create one if you dont already have one by using in the LOAD statement this:

LOAD

  RowNo() AS RowID,

  .....

  .....

Community Browser