Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

omarbensalem
Esteemed Contributor

A way to show alternative/excluded values

Hi all,

Is there a way/ a direct function to show the alternative/excluded values?

if I have 4 dates for example, and a field containg 100 values;

if I select date1;

count(Field) will be 80

if I want to show these 80 fields; I'll just have to use ; concat(Field,',') for example

but what if I wanted to show the 20 excluded fields??

There is the GetExcludedCount function to return thenumber which is 20; but how to show these 20 values?

Thanks

1 Solution

Accepted Solutions
MVP
MVP

Re: A way to show alternative/excluded values

Try stigchel‌'s way or try this

Concat(DISTINCT {1<[WS.Currency] = e([WS.Currency])>} [WS.Currency], ', ')

18 Replies
MVP
MVP

Re: A way to show alternative/excluded values

One way (not direct) is to use this

Concat({<Field = e(Field)>} Field, ', ')

stigchel
Honored Contributor

Re: A way to show alternative/excluded values

concat({1-$}Field,',')


From the help for set Analysis


Examples:

sum( {1-$} Sales )
returns sales for everything excluded by the current selection.

omarbensalem
Esteemed Contributor

Re: A way to show alternative/excluded values

that did not do it:

expression used:

Concat(distinct {<[WS.Currency] = e([WS.Currency])>} [WS.Currency], ', ')

Capture.PNG

omarbensalem
Esteemed Contributor

Re: A way to show alternative/excluded values

I already tried such a thing and it returned all the 147 values (don't know why):

see:

Capture.PNG

MVP
MVP

Re: A way to show alternative/excluded values

Try stigchel‌'s way or try this

Concat(DISTINCT {1<[WS.Currency] = e([WS.Currency])>} [WS.Currency], ', ')

omarbensalem
Esteemed Contributor

Re: A way to show alternative/excluded values

Brilliant as usual

omarbensalem
Esteemed Contributor

Re: A way to show alternative/excluded values

I have yet another question

I have this table:

Capture.PNG

which contains currency list by date

in total there is 176 currencies; what If I wanted to show, not the list of currencies that exist in each date, but the list of those that does not (without selecting any date)

sthig like we were doing but directly within this table

MVP
MVP

Re: A way to show alternative/excluded values

So, you mean to show which currency don't exist on a particular day?

omarbensalem
Esteemed Contributor

Re: A way to show alternative/excluded values

Yes ! with the previous expression; we'll have to select a date to show the currencies that do not exist

I want to show them in a table with the date as a dimension