Set Analysis on Character attributes

[tvsr111]

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

[tvsr111]

any help please ?

[mayuringale25]

Hi @tvsr111 

Refer this link 

https://community.qlik.com/t5/QlikView-App-Development/1-fact-table-2-date-dimensions-1-master-calen...

[tvsr111]

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

[robert99]

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

[tvsr111]

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