topic Re: Set Analysis in Table results in rows with zero value and uncheck Include zero values does not hide them. in App Development

Set Analysis in Table results in rows with zero value and uncheck Include zero values does not hide them.

vawtephens — Mon, 04 Dec 2023 21:43:08 GMT

Using Qlik Sense Server I am trying to filter the results of a TABLE based based on a MEASURE within the table

As found the field is,

Sum(Distinct GoodVials)

I have created the below variable which I have a slider on the sheet to change between 0 and 1000

MinVialOutput

Goal: I want to filter OUT all the rows which have "GoodVials" < MinVialOutput

The closest I've come,

Sum({$<GoodVials = {">$(MinVialOutput)"}>} Distinct GoodVials)

The above results in "GoodVials = 0" for all the rows that don't meet the criteria("GoodVials" > MinVialOutput).

When MinVialOutput = 0, I see all the correct GoodVials, but if MinVialOutput = 100, then all rows of data with MinVialOutput < 100 have GoodVials = 0.

For the tabke, un-Checking the Add-ons->Data handling-> "Include zero values" has no affect on the result.

Please help, this should be simple, but I'm missing something.

Re: Set Analysis in Table results in rows with zero value and uncheck Include zero values does not hide them.

marcus_sommer — Tue, 05 Dec 2023 06:56:04 GMT

It sounds that you don't want to filter GoodVials else the aggregation of them. In this case you will need something like this:

If(Sum(GoodVials)>$(MinVialOutput), Sum(GoodVials))

Re: Set Analysis in Table results in rows with zero value and uncheck Include zero values does not hide them.

vincent_ardiet_ — Tue, 05 Dec 2023 08:25:24 GMT

This is surprising that unchecking "Include zero values" does not remove those rows. You don't have any other measures?
Else, how many dimension do you have? If you have only one (for example [Product]) , you can filter out using a calculated dimension instead, for example something like
=Aggr(Only({$<GoodVials = {">$(MinVialOutput)"}>} Product,Product)

Re: Set Analysis in Table results in rows with zero value and uncheck Include zero values does not hide them.

vawtephens — Tue, 05 Dec 2023 14:42:41 GMT

Hello, I just tried this solution, but used 'Distinct GoodVials'. This results in the rows still being in the table, but with NULL '-' in the Vial Output for entries

>$(MinVialOutput)

Thank you for trying, please see below I'm going to add a solution I've come up with.

Re: Set Analysis in Table results in rows with zero value and uncheck Include zero values does not hide them.

vawtephens — Tue, 05 Dec 2023 14:53:11 GMT

Hello, This is a large table I'm working at improving the visualization. I'm struggling exactly which is a Measure vs. Dimension, but I can see I have about 9 Dimensions and 4 measure. I did work out the following solution.

I have a slider to set the 'MinVialOutput'

And then using a Button with Action "Select values matching search criteria",

Field of GoodVials

And the following Value:

='>$(MinVialOutput)'

I wish I could do this without a 2nd step of Select to Filter.

Re: Set Analysis in Table results in rows with zero value and uncheck Include zero values does not hide them.

marcus_sommer — Wed, 06 Dec 2023 08:52:32 GMT

For me it's further unclear if the limitation should be applied against field-values or an aggregation? If it's against an aggregation you couldn't apply a selection on the aggregated field itself - else it would need to be the fields from the dimensional context in which the aggregation is performed. Probably a customer and/or a product and/or any period-fields.

Beside this such selection isn't mandatory needed and may also have some side-effects. Better is usually to place the condition into all expressions or within at least one dimension (the first one and then hiding NULL).