Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Set-analysis, multiple static ranges

Hi all,

I've been using Denys Smakovskyi method of set analysis to ignore/use specific selections & adding static ranges. The issue I'm having is when I'm trying to use multiple conditions, I'm appending using the "+" set_operator (perhaps you know a better way?)

This will run but ignores the second condition "[OrderStatus]" so returns the sum of all orders (regardless of OrderStatus) that were entered within the specified datetime range


SUM(

{$<$(=Concat({1<$Field-={'[OrderType]'}>}distinct '['&$Field&']=',','))>*1

<[EnteredTimeStamp]={">=$(=timestamp(firstworkdate(Floor(Today()),2)+ 12/24))                                    <=$(=timestamp(firstworkdate(Floor(Today()),1)+12/24))"}>+

<[OrderStatus]={"=$ (=22)"}> /* [22 = Active order] */

}[OrdVal])

Does anyone have an idea what I'm overlooking?

Thanks



Edited: formating

Tags (1)
1 Solution

Accepted Solutions

Re: Set-analysis, multiple static ranges

Or may be just one of these:

Sum({$<$(=Concat({1<$Field-={'[OrderType]'}>}distinct '['&$Field&']=',',')), [EnteredTimeStamp]={">=$(=timestamp(firstworkdate(Floor(Today()),2)+ 12/24))                        <=$(=timestamp(FirstWorkDate(Floor(Today()),1)+12/24))"}>+<$(=Concat({1<$Field-={'[OrderType]'}>}distinct '['&$Field&']=',',')),[OrderStatus]={22}>} [OrdVal])

or

Sum({$<$(=Concat({1<$Field-={'[OrderType]'}>}distinct '['&$Field&']=',',')), [EnteredTimeStamp]={">=$(=timestamp(firstworkdate(Floor(Today()),2)+ 12/24))                        <=$(=timestamp(FirstWorkDate(Floor(Today()),1)+12/24))"}, [OrderStatus]={22}>} [OrdVal])

5 Replies
MVP
MVP

Re: Set-analysis, multiple static ranges

May be like:

SUM(

{$<[OrderStatus]={"=$ (=22)"}, $(=Concat({1<$Field-={'[OrderType]'}>}distinct '['&$Field&']=',','))>*1

<[EnteredTimeStamp]={">=$(=timestamp(firstworkdate(Floor(Today()),2)+ 12/24))                                    <=$(=timestamp(firstworkdate(Floor(Today()),1)+12/24))"}>

}[OrdVal])

Not applicable

Re: Set-analysis, multiple static ranges

Thanks for the suggestion (it executed successfully) but did not change the result.

I can change the OrderStatus to "99999" (no such status) and the results remain the same. It's like it's not reading it at all.


There is a function I dont understand and cannot find documentation on. In the original post it's referred to as "Set Analisys union with correct modified "1"" ie '['&$Field&']=',','))>*1 although the expression will not run without it so I left it in

Re: Set-analysis, multiple static ranges

May be this:

Sum({$<$(=Concat({1<$Field-={'[OrderType]'}>}distinct '['&$Field&']=',','))>*

(1<[EnteredTimeStamp]={">=$(=timestamp(firstworkdate(Floor(Today()),2)+ 12/24))                        <=$(=timestamp(FirstWorkDate(Floor(Today()),1)+12/24))"}>+<[OrderStatus]={22})>} [OrdVal])

or

Sum({$<$(=Concat({1<$Field-={'[OrderType]'}>}distinct '['&$Field&']=',','))>*

1<[EnteredTimeStamp]={">=$(=timestamp(firstworkdate(Floor(Today()),2)+ 12/24))                        <=$(=timestamp(FirstWorkDate(Floor(Today()),1)+12/24))"}, [OrderStatus]={22}>} [OrdVal])

Re: Set-analysis, multiple static ranges

Or may be just one of these:

Sum({$<$(=Concat({1<$Field-={'[OrderType]'}>}distinct '['&$Field&']=',',')), [EnteredTimeStamp]={">=$(=timestamp(firstworkdate(Floor(Today()),2)+ 12/24))                        <=$(=timestamp(FirstWorkDate(Floor(Today()),1)+12/24))"}>+<$(=Concat({1<$Field-={'[OrderType]'}>}distinct '['&$Field&']=',',')),[OrderStatus]={22}>} [OrdVal])

or

Sum({$<$(=Concat({1<$Field-={'[OrderType]'}>}distinct '['&$Field&']=',',')), [EnteredTimeStamp]={">=$(=timestamp(firstworkdate(Floor(Today()),2)+ 12/24))                        <=$(=timestamp(FirstWorkDate(Floor(Today()),1)+12/24))"}, [OrderStatus]={22}>} [OrdVal])

Not applicable

Re: Set-analysis, multiple static ranges

THAT'S IT!!!! stalwar1‌ you legend


Sum({$<$(=Concat({1<$Field-={'[OrderType]'}>}distinct '['&$Field&']=',',')),

[EnteredTimeStamp]={">=$(=timestamp(firstworkdate(Floor(Today()),2)+ 12/24))                        <=$(=timestamp(FirstWorkDate(Floor(Today()),1)+12/24))"},

[OrderStatus]={22}>

} [OrdVal])

The entire expression needs to be within a single "< >" with commas delimiting the filters.

Thank you so much