Table with conditions

[williamdame]

Hi, I have a little problem that I can´t get a solution.

I have a table, that shows ID and Status:

I want to generate a table only with ID´s that didn´t get the status of DELIVERED not even once. 

In other words, I want to gerenerate a table only with 1 Column with the ID´s  444 and 777, since they don´t have the status of Delivered. 

Anyone can help me with that?

[Oleg_Troyansky · Accepted solution]

Hi,

You can use Set Analysis to filter out those IDs that had the DELIVERED status. And, since you only want one column, it has to be a Dimension, so you have to use the AGGR() function in order to calculate it as a Dimension:

Something like this:

AGGR(

       ONLY({<ID=-P({<Status={'DELIVERED'}>})>} ID)

,ID)

I know this must look very crypted to a beginner. Too much to explain in one message. Check out my upcoming lesson on Set Analysis and AGGR() at the virtual Masters Summit for Qlik:

https://MastersSummit.com

Cheers,

[williamdame]

Thanks, it worked very well!!

I´m still trying to understand how to deal with Sense, I will look at your website. Thanks a lot.

I had another issue yesterday that I Still can´t get to a solution.  If you could help with that too I would apreciate. 

the link:

https://community.qlik.com/t5/New-to-Qlik-Sense/Contar-distintos/m-p/1744753#M171479

[Kushal_Chawda]

@williamdame  You can also use below.

If you have measure in your table then use below. Assuming sum(Sales) as measure. You can use the same set modifier in your actual measure

Sum({<ID=e({<Status={'Delivered'}>})>}Sales)

If there is no measure in your table then  create new one as below

=aggr(only( {<ID=e({<Status={'Delivered'}>})>}ID),ID)