topic Re: Limiting Straight Table in QlikView

Limiting Straight Table

quilldew — Wed, 25 Nov 2020 16:16:04 GMT

I'm probably being stupid here, but I can't find a solution to this simple problem, and I can't find any topics that directly address it either, it's no doubt because the solution is so simple.

I have a straight table, and one of the dimensions is age (as num). I want to limit the table so that it only shows values, for all dimensions in the table, where the age is less than or equal to 16.

I've tried dimension limits and I've tried a calculated dimension, i'm obviously stupid this morning. Please help me.

Re: Limiting Straight Table

kenphamvn — Tue, 26 Sep 2017 09:15:59 GMT

Try this?

Create New dimension like

=if(DimensionAge>=16,DimensionAge,null())

and uncheck "Include Null Values" in Dimension Properties Panel

Regards

Re: Limiting Straight Table

quilldew — Tue, 26 Sep 2017 10:00:17 GMT

Hi,

Unfortunately that didn't work. It still lists all ages. Also by "and uncheck "Include Null Values" in Dimension Properties Panel" I assume mean to tick the "Suppress When Value Is Null" box?

Re: Limiting Straight Table

marcus_sommer — Tue, 26 Sep 2017 10:22:10 GMT

You might need to add a dimension-condition like the one above in more than a single-dimension. Further you need to check your expressions - an expression with set analysis like sum({1} TOTAL AnyValue) could display dimension-values which are excluded by conditions/selections.

Another way could be to exclude this through set analysis within the expressions like:

sum({< DimensionAge = {"<=16"}>} AnyValue)

- Marcus

Re: Limiting Straight Table

effinty2112 — Tue, 26 Sep 2017 10:22:17 GMT

Hi,

try this calculated dimension:

aggr(if(DimensionAge>=16,DimensionAge),DimensionAge)

In the dimensions tab check the option to Suppress When Value Is Null.

Regards

Andrew

Re: Limiting Straight Table

quilldew — Tue, 26 Sep 2017 10:29:00 GMT

I'm unfortunately not using any expressions within the table. I would definitely use set analysis if I was though thanks.

Re: Limiting Straight Table

quilldew — Tue, 26 Sep 2017 10:31:09 GMT

That worked perfectly thank you. I'm also grateful it took an aggr function, which I can pretend are difficult, to solve this.

Any idea why using the Dimension Limits tab and the second option didn't work? I assumed that tab was created for situations such as this?

Re: Limiting Straight Table

quilldew — Tue, 26 Sep 2017 12:36:44 GMT

I just tried to convert the correct answer to also limit a straight table by a text value.

=aggr(if([myfield]"mytext",[myfield]),[myfield])

but unfortunately it didn't work.

Is it the same principle, or would it be worth me creating another thread?

Re: Limiting Straight Table

effinty2112 — Tue, 26 Sep 2017 12:51:23 GMT

Hi Daemon,

The general method should still work for text conditions. For example

=Aggr(if(wildmatch([my field],'A*'), [my field]),[my field])

should restrict your dimension values to those stating with 'A'.

Double check your syntax or post the actual definition so we can see.

cheers

Andrew