Skip to main content

App Development

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

Announcements
QlikWorld 2023, a live, in-person thrill ride. April 17 - 20, 2023, in Las Vegas! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
joey_lutes
Partner - Creator
Partner - Creator

Straight Table Calculated Dimension with Set Analysis non-numeric values

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?

Labels (4)
1 Solution

Accepted Solutions
sunny_talwar

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)

 

View solution in original post

4 Replies
Taoufiq_Zarra

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,

 

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
sunny_talwar

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)

 

joey_lutes
Partner - Creator
Partner - Creator
Author

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!

zied_ahmed1
Specialist
Specialist

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