Selecting specific values in Measure-Columns for Reports

[Sapphire]

Hi there!

I have an Issue regarding the use of Measures (and maybe a bit of a lack of understanding as to what the underlying reasons are for this Issue to arise).

My Task:

I have a set of data, containing the IDs of Cars and different codes for service options that have to be done to this cars, as well as the status of these service options. One ID can either have one or more of the same service options at the same time. Now I want to create a table that lists all Car-IDs that have more than one service option open at the same time.

To achieve this, I wanted to simply create a measure counting all service options per ID that are in a defined set of status. This works, but so far shows also the IDs that only have 1 service option per ID.

My Problem:

I don`t know how to filter measures, since I can`t just say "select all values bigger 1". When putting it in an expression, I get the error "invalid Dimension".

My second try was to go via an expression like i.e. "=if(count(serviceOption)>1, serviceOption,null())" and then simply hide zero-values which, however, only works with dimensions, not measures.

So now my Question sums up basically into: How can i select or filter specific values in measure columns in tables (Like either "show only value "x" or "show greater than x")?

I once again hope on your ability to help me! 🙂

Faithful regards,

Daniel

[henrikalmen]

I suggest you create a new field in the data model, that holds a counter for each ID.

[Sapphire]

Hello Henrikalmen,

I agree this would be most likely the best solution. However, I have the problem that in our company I can`t just modify the loading script or data model due to different reasons and our policy, hence I have to rely on "front end solutions", sadly.

[henrikalmen]

You can enter an expression when doing a search in the ID dimension. That way it's possible to find the IDs that match a search criteria using other fields.

This might be a start:

https://help.qlik.com/en-US/sense/May2024/Subsystems/Hub/Content/Sense_Hub/Selections/SelectionsTool...

https://community.qlik.com/t5/Member-Articles/Examples-of-Advanced-Search-Filters/ta-p/1594779

https://ometis.co.uk/blog-news/answer-ad-hoc-questions-with-advanced-search-expressions-in-qlik

[Lech_Miszkiewicz]

Hi @Sapphire 

Why dont you use set analysis with advanced search?

Count({<ID={"=Count(ServiceID)>1"}>}WhateverYouWantToCount)

Sum({<ID={"=Count(ServiceID)>1"}>}WhateverYouWantToSum)

EDIT:

I missed important "=" sign, added now

cheers

[Sapphire]

Heya,
Thanks for your suggestions, I will look into that, maybe it might help. And, even if not, at least I will have learned something new, then.

[Sapphire]

Hi,
thank you for the suggestion, I'll try that and see if it works. Just for my understanding: This solution should work both in the App and as a filter-condition in nPrinting, is that correct? I mean, syntax-wise are both (advanced search in QlikSence and advanced search in nPrinting) the same, as far as I understand it?

[Lech_Miszkiewicz]

I dont understand your question. Syntax is sligthly different as what I suggested is a set expression in set analysis which is not the same as "advanced search" search filter or advanced search NPrinting filter.

The solution I suggested was to implement this in the Qlik Sense object as a measure. Object is prefiltered to only show values where there are more than 1 services. At this stage you just bring this object to NPrinting and you dont need to filter anything.

Then if you wish to apply filter in NPrinting using Advanced search the question is which dimension you want to filter and if applying filter on such dimension is sufficient to meet criteria/granularity of the data. I have explained those concepts in my blog post here:

• https://nprintingadventures.com/2021/09/23/filters-in-nprinting-how-hard-can-it-be-part-2-or-mode/
• and little bit  here: https://nprintingadventures.com/2019/06/26/nprinting-filters-part-3-advanced-search/

It would very much help if you had particular example we could work with to explain those concepts. Maybe create ctrl+0+0 app and lets talk using that example.

cheers

[Sapphire]

My question regarding the filter was a more general question to see wether or not I could apply this syntax in nPrinting as well. That thought came to mind because I read your reply just as I was setting up a filter. But indeed, my underlying issue was to find a solution directly in sense. Sorry for the confusion.

As for making an example, let that be the raw Data:

VehicleID	ServiceID	ServiceDescription
123	CLE	CleanWindows
234	CLE	CleanWindows
234	CLE	CleanDoor
456	CLE	CleanDoor
567	CLE	CleanWindows

Now, my first step was to just get a second table but without the ServiceDescription and instead the expression "count(ServiceID)". Hence I get for the Vehicle ID "234" the value "2" and for the rest the value "1".

What I do want and need however, is that only the rows with at least a "2" i the "count(ServiceID)" are shown.
If I understand your syntax right, to achieve that, I need to replace "count(ServiceID)" with
"Count({<ID={"Count(ServiceID)>1"}>}ServiceDescription)".
This I tried, and although I get no error, it basically still shows me everything. Most likely I am overlooking something simple, here, but so far I don't see it, yet.

[Lech_Miszkiewicz · Accepted solution]

Hi @Sapphire 

My bad - I missed "=" sign in my previous post hence it didnt work for you there - I have updated it in my previous post.

Here you have your example. Important thing is to understand the context here. In your exxample You can only filter "count(ServiceID)>1" against the VehicleId dimension. Think about using advanced search with measure like about creating virtual table in memory with the dimension you filter, measure and condition. In your case it is:

1. Dimension: VehicleID
2. Measure: Count(ServiceID)
3. Condition > 1

This can be validated also as a filter on VehicleID field - as you can see it returns 234 as the only result meeting the criteria:

And below whole example:

And In NPrinitng filter would be just:

This example is very easy and does not create data granularity challenges which i described in my blog post. Those have to be considered when using more advanced searches.

Attaching sample app so you can have full picture. Hope this is now super clear.

cheers