Need a report that counts orders placed after 6pm daily

[#1]

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

[sunny_talwar]

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)

[#3]

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)

[#4]

Thanks all.  I can't make changes to the load script very easily.  I'm trying the set analysis statements now.

[sunny_talwar · Accepted solution]

How about this:

Count({$<OrderID = {"=Hour(ELS.FirstSubmission) >= Hour(MakeTime(18))"}>} OrderID)

[#6]

Thanks. That worked. I would never have thought to put the OrderID = that expression.  Can you please explain your logic?  Very much appreciated.

[sunny_talwar]

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

[#8]

Thank you for your  time and explanation.

[sunny_talwar]

No problem at all

[marygrace]

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!