Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I'm trying to create a simple report that counts the number of orders placed after 6 pm daily.
I have this expression that works with an if statement but won't work with set analysis.
If( Hour(ELS.FirstSubmission) >= 18, Count(Distinct OrderID))
I'm using this set analysis statement below but it shows an error which I can't figure out.. It's supposed to count orders with times greater than 6pm.
count({$<Hour(ELS.FirstSubmission)= {">= 18"}>}OrderID)
Any suggestions to correct. Thanks.
Don Saluga
How about this:
Count({$<OrderID = {"=Hour(ELS.FirstSubmission) >= Hour(MakeTime(18))"}>} OrderID)
May be create a field in the script using the Hour function:
Hour(ELS.FirstSubmission) as Hour_ELS.FirstSubmission
and then use this:
Count({$<Hour_ELS.FirstSubmission = {">=$(=Hour(MakeTime(18)))"}>} OrderID)
you can use expression on value field Hour(ELS.FirstSubmission)
or you transform on scrip and use on set analisys
or implement a expression on your set analisys
=count({$<ELS.FirstSubmission= {">=$(=date( Floor( ELS.FirstSubmission)+ (1/24)*18,TimestampFormat))"}>}OrderID)
Thanks all. I can't make changes to the load script very easily. I'm trying the set analysis statements now.
How about this:
Count({$<OrderID = {"=Hour(ELS.FirstSubmission) >= Hour(MakeTime(18))"}>} OrderID)
Thanks. That worked. I would never have thought to put the OrderID = that expression. Can you please explain your logic? Very much appreciated.
Here we are using a search string to pick those OrderID where this (=Hour(ELS.FirstSubmission) >= Hour(MakeTime(18))) is met. So in essence create a straight table with OrderID as your dimension and =Hour(ELS.FirstSubmission) >= Hour(MakeTime(18)) as expression and you will see that all those OrderID where the expression is giving -1 will be included and 0 (when Suppress Zero Value is unchecked) will not be included. Read about search string here: A Primer on Set Analysis
Thank you for your time and explanation.
No problem at all
HI Sunny,
I am working in the same case, I am counting the orders specific date and time... I do not have idea how filter the selection
I already have by date: but I am not sure how add by Hours.
I send you my set analisis actual without Hour...
Count(
{$
<Date={"$(=Date(max(Date),'DD/MM/YYYY'))"}, _Id_Flag={0},Estatus_Cobranza= {'EN SURTIDO','FACTURADO','ENTREGADO','CONSOLIDADO'}, Liberado={1}, TipoDeEntrega -= {'SOLO FACTURA'},Year={$(vAñoActual)},Month=,Week=>
+
<Date={"$(=Date(max(Date)-1,'DD/MM/YYYY'))"}, _Id_Flag={0},Estatus_Cobranza= {'EN SURTIDO','FACTURADO','ENTREGADO','CONSOLIDADO'}, Liberado={0}, TipoDeEntrega -= {'SOLO FACTURA'},Year={$(vAñoActual)},Month=,Week=>
}
distinct Numero_OrdenVenta) )
I would like to filter by specific Hour: All Orders delivery until 15 Hours
I have not idea if I just to need add Hours into actual set analisis
Thank you!