topic Re: How to exclude one data set from another in App Development

How to exclude one data set from another

Peony — Wed, 04 Dec 2024 19:09:17 GMT

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?

Re: How to exclude one data set from another

Peony — Wed, 04 Dec 2024 19:54:46 GMT

Solution could looks like this

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

Re: How to exclude one data set from another

QFabian — Wed, 04 Dec 2024 20:25:07 GMT

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))

Re: How to exclude one data set from another

chriscammers — Wed, 04 Dec 2024 20:27:12 GMT

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)

Re: How to exclude one data set from another

Peony — Wed, 04 Dec 2024 20:39:40 GMT

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

Re: How to exclude one data set from another

QFabian — Wed, 04 Dec 2024 20:40:58 GMT

glad to help!

Re: How to exclude one data set from another

Peony — Wed, 04 Dec 2024 21:06:31 GMT

@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

Re: How to exclude one data set from another

chriscammers — Thu, 05 Dec 2024 14:13:07 GMT

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

Items from last year <Item = P({<YearField = {'$(=Max(YearField) - 1)'}>})>
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/SetAnalysis/set-modifiers-set-operators.htm 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

Re: How to exclude one data set from another

Peony — Sat, 07 Dec 2024 10:51:25 GMT

@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.