Skip to main content
Announcements
New: No-code data prep in Qlik Cloud Analytics™ TAKE A TOUR
cancel
Showing results for 
Search instead for 
Did you mean: 
Peony
Creator III
Creator III

How to exclude one data set from another

Hi All.
Could you please give me a hint how to calculate how may Items from period 1 don't have values in period 2?

This should be 3 as an count result (Item2, Item3, Item6). But is there any option to calculate this not using If statement I use currently Sum(If( Len(Period1)>0 and Len(Period2)<1,1).

Maybe exist something like Count({<Period1-=Period2>}Item)? Or something other solution that allows to take one data set and exclude from it all items that exists in other data set?

Peony_2-1733339091383.png

 

Peony_0-1733338354615.png

 

Labels (1)
3 Solutions

Accepted Solutions
Peony
Creator III
Creator III
Author

Solution could looks like this 

=Count({<[Period1]-={"","NULL"},[Period2]={"","NULL"}>}Item)

View solution in original post

QFabian
Specialist III
Specialist III

Hi @Peony , check this another option, with this script, that can make the difference between Null y BLANK, in this example, with the INLINE, the empty field is recognized as BLANK = ''

Test_Set:
Load * INLINE [
Item, Period1
Item1, 1
Item2, 2
Item3, 2
Item4,
Item5, 5
Item6, 2
Item7,
];

Test_Set_2:
Load * INLINE [
Item, Period2
Item1, 3
Item2,
Item3,
Item4, 4
Item5, 6
Item6,
Item7, 9
];


exit script;

and the chart expression formula :

count(if( Period2 = '', Item))

QFabian_0-1733343841566.png

 

QFabian

View solution in original post

chriscammers
Partner - Specialist
Partner - Specialist

Oh, let's be clear you are trying to show the items that sold in a particular time period but not in a second time period, for example Items that have not sold this year?

So you need to establish your sets using the P and E functions returns items based on the set analysis used in the function P is the "possible" values and E is the "Excluded" values

  1. Items from last year <Item = P({<YearField = {'$(=Max(YearField) - 1)'}>})>
  2. Items that did not sell This year <Item = E({<YearField = {'$(=Max(YearField))'}>})>

Now combine the two using set operators in your count function

Count({<Item = P({<YearField = {'$(=Max(YearField) - 1)'}>})> - <Item = E({<YearField = {'$(=Max(YearField))'}>})>}Item)

I will admit this is an area Qlik should expand on it's documentation but this page https://help.qlik.com/en-US/sense/May2024/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetAnalysi... provides the starting blocks you will need.

 

As for doing the selection based on a range of dates the syntax without calculating it is something like this

Count({<DateField = {">=1/1/2023<=12/31/2023"}>} Item)

If you want to dynamically calculate the range of dates then you have to use dollar sign expansion in place of the search string between the double quotes $(='>=' & YearStart(Max(DateField),-1)) & '<=' & YearEnd(Max(DateField),-1)))

I suggest you start off with small chunks of this, hard code it first and then replace the hard coded parts with dynamic formulas. That way you'll know what the various expressions need to return when things get complicated.

 

Good Luck

Chris

 

View solution in original post

8 Replies
Peony
Creator III
Creator III
Author

Solution could looks like this 

=Count({<[Period1]-={"","NULL"},[Period2]={"","NULL"}>}Item)

QFabian
Specialist III
Specialist III

Hi @Peony , check this another option, with this script, that can make the difference between Null y BLANK, in this example, with the INLINE, the empty field is recognized as BLANK = ''

Test_Set:
Load * INLINE [
Item, Period1
Item1, 1
Item2, 2
Item3, 2
Item4,
Item5, 5
Item6, 2
Item7,
];

Test_Set_2:
Load * INLINE [
Item, Period2
Item1, 3
Item2,
Item3,
Item4, 4
Item5, 6
Item6,
Item7, 9
];


exit script;

and the chart expression formula :

count(if( Period2 = '', Item))

QFabian_0-1733343841566.png

 

QFabian
chriscammers
Partner - Specialist
Partner - Specialist

I'd like to point you to an alternate solution that I think will work better in the long run. In the Set Analysis help page there is some mention of set modifiers with set operators

 set modifiers set operators 

//Expressions using set analysis
/*In Period1*/
=Count({<Item = {"=Sum(Period1)>0"}>}Item)

/*In Period2*/
=Count({<Item = {"=Sum(Period2)>0"}>}Item)

/*In Period1 but not Period2*/
=Count({<Item = {"=Sum(Period1)>0"}>-<Item = {"=Sum(Period2)>0"}>}Item)

 

Peony
Creator III
Creator III
Author

@QFabian Thank you for your idea! It is so elegant!😍

QFabian
Specialist III
Specialist III

glad to help!

QFabian
Peony
Creator III
Creator III
Author

@chriscammers Your idea is definitely what I need in a perfect run! But I'm a bit confused how I can apply your example to my actual data. If you would not mind, could  you please help to rewrite final expression? Because I'm lost with syntaxis. Just for Period1, how does formula will looks like? 
Period1 is some date set that could be changed in dashboard's calendar. It has dome start_date and end_date.


=Count({<Item = {"=Sum(Date={">$(=$(vDateStart))<=$(=$(vDateEnd))"})>0"}>}Item)
 

Will such syntaxis be correct? I'm confused a bit in using Sum inside set expression especially in combination with dates

 

chriscammers
Partner - Specialist
Partner - Specialist

Oh, let's be clear you are trying to show the items that sold in a particular time period but not in a second time period, for example Items that have not sold this year?

So you need to establish your sets using the P and E functions returns items based on the set analysis used in the function P is the "possible" values and E is the "Excluded" values

  1. Items from last year <Item = P({<YearField = {'$(=Max(YearField) - 1)'}>})>
  2. Items that did not sell This year <Item = E({<YearField = {'$(=Max(YearField))'}>})>

Now combine the two using set operators in your count function

Count({<Item = P({<YearField = {'$(=Max(YearField) - 1)'}>})> - <Item = E({<YearField = {'$(=Max(YearField))'}>})>}Item)

I will admit this is an area Qlik should expand on it's documentation but this page https://help.qlik.com/en-US/sense/May2024/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetAnalysi... provides the starting blocks you will need.

 

As for doing the selection based on a range of dates the syntax without calculating it is something like this

Count({<DateField = {">=1/1/2023<=12/31/2023"}>} Item)

If you want to dynamically calculate the range of dates then you have to use dollar sign expansion in place of the search string between the double quotes $(='>=' & YearStart(Max(DateField),-1)) & '<=' & YearEnd(Max(DateField),-1)))

I suggest you start off with small chunks of this, hard code it first and then replace the hard coded parts with dynamic formulas. That way you'll know what the various expressions need to return when things get complicated.

 

Good Luck

Chris

 

Peony
Creator III
Creator III
Author

@chriscammers Thank you much for you time and detailed explanation.🤝 It is really makes my set analysis understanding better and I my deal with more complicated solutions.