topic Straight Table Calculated Dimension with Set Analysis non-numeric values in App Development

Straight Table Calculated Dimension with Set Analysis non-numeric values

joey_lutes — Sat, 16 Nov 2024 03:54:06 GMT

I have many use cases of trying to limit data in a straight table inside Qlik Sense. One of the more common one is to only show rows of data where a value is blank in a different column.

IE:

Show me a list of 'Products' where 'Customer' field is blank.
I'm trying to do this with Set Analysis (vs 'IF'). What I THINK should work is:

=aggr(only({$<Products= {"=len(trim(Customer))=0"}> Products}, Products)

Unfortunately, it gives me "Invalid Dimension"

Similar use case would be:
Show me a list of 'Products' where 'Customer' = 'ABC'.

=aggr(only({$<Customer= {"ABC"}> Products}, Products)

I'm assuming syntax is the culprit. Exhaustive searching has led me here. Thoughts?

Re: Straight Table Calculated Dimension with Set Analysis non-numeric values

Taoufiq_Zarra — Fri, 06 Dec 2019 19:06:41 GMT

Hi,

First in the syntax there is a typing error I think before "Products" and comma you have to close the parenthesis of only.
the same in the second script.

And then in the dimension why not just use the dimension calculate :

=if(Len(Trim([Customer])) = 0,Products, Null())

And for the second one

=if(Customer = 'ABC',Products, Null())

Cheers,

Re: Straight Table Calculated Dimension with Set Analysis non-numeric values

sunny_talwar — Fri, 06 Dec 2019 19:15:20 GMT

I think you need to replace one of the } to ) and also, you have a missing } after }>

=aggr(only({$<Products= {"=len(trim(Customer))=0"}> Products}, Products)

Try this

=Aggr( Only({$<Products = {"=Len(Trim(Customer)) = 0"}>} Products) , Products)

Re: Straight Table Calculated Dimension with Set Analysis non-numeric values

joey_lutes — Fri, 06 Dec 2019 19:52:13 GMT

Thanks Sunny. I think that is correct. It didn't give me an error this time.

However, I realized, my data model is such that I'm going to have to go at it a different way. 'Customer' (in this example) is not in the same table - and is linked several tables away.

I appreciate the response as always!

Re: Straight Table Calculated Dimension with Set Analysis non-numeric values

zied_ahmed1 — Mon, 16 Dec 2019 10:54:43 GMT

Hello,

This one is more simple for you :

=aggr(if (Len(Trim( Customer))=0,Products,Null()),Products)

Don't forget to not include null values for Products

Dimension

Best regards