Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
berndjaegle
Creator II
Creator II

Set Analysis - Ignore all fields from one table

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.

Labels (2)
1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

It is possible with something like:

sum({< $(=concat({1< $Table = {'Calendar'}>} $Field, ' ,')) >} Amount)

- Marcus

View solution in original post

16 Replies
Somasundaram
Creator III
Creator III

Hi,

we are not able to omit the data from particular Data table . There is no option in Set analysis. 

 

 


-Somasundaram
berndjaegle
Creator II
Creator II
Author

Thanks for you reply.... also I am not satisfied about it 😐
marcus_sommer
MVP & Luminary
MVP & Luminary

It is possible with something like:

sum({< $(=concat({1< $Table = {'Calendar'}>} $Field, ' ,')) >} Amount)

- Marcus

View solution in original post

sunny_talwar

And in case you have spaces in your field names, then add square brackets around $Field like this

Sum({<$(=Concat({1<$Table = {'Calendar'}>} '[' & $Field & ']', ' ,'))>} Amount)

 

berndjaegle
Creator II
Creator II
Author

Thank you Marcus! Solved my problem!

sudhirpkuwar
Partner
Partner

Hi Sunny,

I want only one particular field to affect the expression result. No other selection should affect.

For ex. When select anything in Status field only than the expression should change, rest any other selection should not affect it.

Is there any way to achieve this?

Thanks in advance.

marcus_sommer
MVP & Luminary
MVP & Luminary

You could modify the above approach by removing the table-condition by fetching the fields which will return all fields and from this you replaced your field with nothing, something like:

Sum({<$(=replace(Concat('[' & $Field & ']', ','), '[Status],', ''))>} Amount)

- Marcus

lalita_sharma
Contributor III
Contributor III

I have a data model which looks something like below

lalita_sharma_0-1631105172821.png

I am trying to display a straight table on my dashboard which will have columns from table dim_raidlog risks

something like below(only few columns included)

Risk IdWorkstreamRisk LevelRisk RAGRisk ImpactRisk LikelihoodRisk Response
R059M&RWorkstreamCLOSEDMediumMediumMitigate
R068M&RWorkstreamCLOSEDMediumMediumAccept
R069M&RWorkstreamCLOSEDMediumMediumAccept
R070M&RWorkstreamGREENMediumMediumAccept
R071M&RWorkstreamCLOSEDLowMediumMitigate
R108M&RWorkstreamCLOSEDMediumMediumAccept

 

Workstream column has an expression like below:

=IF(GetSelectedCount(Workstream)=0,
AGGR(only({$<[Workstream Id]={$(vCalc_StatusPack_DefaultWorkstreamID)},[KPI name]={3}, [M0 Finish Month]=,[M1 Finish Month]=>}[Workstream]),
[Risk Id],[Workstream],[Risk Level],[Risk RAG],[Risk Impact],[Risk Likelihood],[Risk Response]),
AGGR(only({$<[KPI name]={3},[M0 Finish Month]=,[M1 Finish Month]=>}[Workstream]),
[Risk Id],[Workstream],[Risk Level],[Risk RAG],[Risk Impact],[Risk Likelihood],[Risk Response]))

I don't want the selections on [M0 Finish Month] and [M1 Finish Month] fields to my table on the dashboard.

That is why I have included them in the above expression [M0 Finish Month]=,[M1 Finish Month]=

But still my dashboard table goes blank when I make any selection on these fields. Could someone please help me understand how can I achieve the desired results.

marcus_sommer
MVP & Luminary
MVP & Luminary

The association between the involved tables per link-table might not be sufficient to create the wanted view.

Beside this your aggr() has no outer aggregation and is therefore not really an expression else more a calculated dimension. There are scenarios in which it may work especially by an additionally use of TOTAL and/or NODISTINCT but it's not really quite right. The classical aggr() should more look like:

sum({ Conditions } aggr(sum({ Conditions } Field), Dim1, Dim2))

Further are you sure that you need an aggr() at all? And then with so many dimensions? Aggr() should be only used if there are no other ways to calculated the wanted results and if you could accept the performance-impact (they could be very heavy especially by large datasets and link-table approaches and the use of many dimensions).

- Marcus