Set Analysis - Ignore all fields from one table

[berndjaegle]

Hi guys,

looking for a smart solution to ignore selections from one table of my data model.

The data model is with a link table and I want to ignore one table for one KPI.

I know this option: 

sum({$<[Date]=,[City]=,[Client]=,[Amount]=>} Amount)

I don't want to mention all fields explicit. 

Something like

sum({$<[Table.$Fields]=>} Amount)

Any suggestions?

Thank you.

[lalita_sharma]

Hi Marcus

Thank you for prompt response.

Yes you are correct it is more of a calculated dimension. I had to use AGGR and ONLY so that I can set some default filters on [Workstream Id] and [KPI Name] attributes. Not sure if there is a smarter way to do that in a straight table that has only dimensions.

[marcus_sommer]

Maybe moving the logic to a normal expression within the table is more suitable. Within View you could mix the dimension/expression columns like you want and also to use some for calculations and then hiding them within the view - by Sense I don't know if the same functionality is available but a playing a bit with the features may be helpful.

- Marcus

[lalita_sharma]

Hi Marcus

Thank you so much for the suggestion. 

Now I am creating a measure as below, which I will hide later

=IF(GetSelectedCount(Workstream)=0,
Sum({$<[Workstream Id]={$(vCalc_StatusPack_DefaultWorkstreamID)},[KPI name]={3}>} 1),
Sum({$<[KPI name]={3}>} 1))

now the issue is that even after having a condition of [Workstream Id]={$(vCalc_StatusPack_DefaultWorkstreamID) in above expression, it shows all workstreams even when no workstreams are selected.

What is incorrect in my expression?

[marcus_sommer]

Often it's helpful to check each part separately - means for example including:

GetSelectedCount(Workstream)

$(vCalc_StatusPack_DefaultWorkstreamID)

concat([Workstream Id], ' + ')

as expression within the table, too.

Further make sure that [Workstream Id] is the right field and correctly spelled - Qlik is here case-sensitiv and if it's wrong and/or there are no values else only NULL's within - the set analysis will ignore it completely.

- Marcus

[lalita_sharma]

Hi Marcus

Thanks for your suggestions. Very helpful.

I suppressed the zero values, and now the expression is working fine.

I had checked each part separately, they were working as expected. But only when I try to combine them, the expression shows 0 in place where Workstream is not equal to default. Ideally I would have expected it to filter out everything that did not satisfy the condition in the set expression.

Thanks

Lalita

[marcus_sommer]

It's difficult to guess what might be different to your expectation. You could try to fetch the zero and replacing it with null() - maybe it's more suitable for your view.

- Marcus

[lalita_sharma]

Hi Marcus

Thank you so much

That was really helpful.

-Lalita