Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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])
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])
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
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])
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])
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