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
Highlighted
MVP

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

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

MVP

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

Highlighted
MVP

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