Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Set Analysis on Character attributes

Hi,

Hope you are doing good.

I need a help with Set Analysis on character (Dimension nature) Attributes

 

* Set Analysis does work on Measures when using SUM() & COUNT() functions

Measures:

Amount=sum({<[StartDate]={"<=$(varInputDate)"}, [EndDate]={">=$(varInputDate)"}>} OrderAmount)

QtyCount=count(distinct {<[StartDate]={"<=$(varInputDate)"}, [EndDate]={">=$(varInputDate)"}>} OrderAmount)

 


* But Set Analysis on Character (Dimension) attributes not working

Dimensions:
CustomerName= aggr(only({<[StartDate]={"<=$(varInputDate)"}, [EndDate]={">=$(varInputDate)"}>} CustName), CustName)
StoreLocation= aggr(only({<[StartDate]={"<=$(varInputDate)"}, [EndDate]={">=$(varInputDate)"}>} StoreLoc), StoreLoc)
OrderDate= aggr(only({<[StartDate]={"<=$(varInputDate)"}, [EndDate]={">=$(varInputDate)"}>} OrderDate), OrderDate)

 


We have a specific scenario where we need to apply this set analysis on Dimension columns also along with Measures.
we will use these on charts and table visualizations tooo

the expressions shown are only sample to explain the issue

 

5 Replies
Highlighted
Contributor II
Contributor II

any help please ?

Highlighted
Partner
Partner

Highlighted
Contributor II
Contributor II

Hi,

I don't see anything in that link relevant to Set Analysis for a Dimension (Character) attribute

 

What I need, how to apply Set Analysis {} function on a Character attribute......

I dont want to use IF() condition,  only need to apply Set Analysis functions {}

 

please help

Highlighted
Specialist III
Specialist III

Hi

I don't think you can wrap aggr (or only) around a set analysis statement in this way

I'm also unsure why you don't want to use an if statement. Sometimes if works better as explained in this post

https://community.qlik.com/t5/Qlik-Sense-App-Development/P-and-E-Syntax-issue/td-p/106074

 

Highlighted
Contributor II
Contributor II

Hi

 

When I created a Derived Dimension with "IF" condition based on a Fact table column,  and on the Visualization, when I select a one of that Dimension values, that forces 2 selections to be made implicitly 

 

For example, if

Fact table has: Sale_Country, Sale_Year, Product_Key, Sale_Amount,.....

Dimension has; Product_Key , Product,.....

I created derived Dimension as :  Product= if(sale_country='UK' and sale_year>2016, Product, null())

 

            with this calculation when the visualization is first opened, it show data correctly.

 

But When I select one of the Product values,   Immediately 3 selection are being added at the top on Sale_Country , Sales_Amount

These additional selections are unwanted and they are being added bcoz the Dimension is using those Fact columns internally.

 

That is the reason, I somehow want to use Set Analysis on a Dimension attribute and get list of values based on condition