Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Solution could looks like this
=Count({<[Period1]-={"","NULL"},[Period2]={"","NULL"}>}Item)
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))
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
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
Solution could looks like this
=Count({<[Period1]-={"","NULL"},[Period2]={"","NULL"}>}Item)
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))
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
//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)
@QFabian Thank you for your idea! It is so elegant!😍
glad to help!
@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
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
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
@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.