Count Orders specific Hour selected

[marygrace]

Hello,

I need to count the orders Delivery and Processed in specific selected Hours. I have no idea if I need to create calendar time and link direct to fact table. I already have the Delivery_Hour field and Processed_Hour field into Fact table.

Count(

     {$

          <Date={"$(=Date(max(Date),'DD/MM/YYYY'))"}, _Id_Flag={0},Estatus_Cobranza= {'EN SURTIDO','FACTURADO','ENTREGADO','CONSOLIDADO'}, Liberado={1}, TipoDeEntrega -= {'SOLO FACTURA'}

     }

     distinct Numero_OrdenVenta)

How I can add the Hour selection into my set analysis ?

Thank you!

[Mark_Little]

HI Maria,

If your set analysis is working, a filter box should allow you to filter down to the given hour selected.

Depending on the Format of your time, if you want to just select a given hour and ignore minutes make a new field in Script.

Say it is 10:12

Used LEFT(Time,2) AS Hour

Then use this new field in a filter.

Mark

[marygrace]

Hi Mark,

If I have two fields I wonder if is better to use the calendarTime filed Hour to filter and compare the Hour (fields). Any idea how to add hour time information into my set analysis

Thank you!

e

[agigliotti]

why do you want to use set analysis ?

try to use your Hour field as filter as well as Date field.

[marygrace]

Well,

Maybe my data model is not good, because I already use Hour field as  filter and does not filter the Order by  Delivery_Hour  and Processed_Hour. This is the reason I want to use set analysis to specified each field hour between CalendarTime filder Hour to  Order by  Delivery_Hour  and Processed_Hour fields.

I wonder if I need to have link between calendar time.Hour  to Fact Table both fields (Delivery_Hour  and Processed).

Because so far the filter does not know what I want to filter.

[agigliotti]

let's try adding:

Delivery_Hour = {"={$(=Concat(Distinct Hour,','))"}, Processed_Hour = {"={$(=Concat(Distinct Hour,','))"}

to your set analysis expression.

[Anonymous]

I'm assuming your %Key_Fecha doesn't represent time at all.  So using your Hour field as a filter doesn't do what you want.  You can either do some thing with a canonical date type logic, except for hours. Canonical Date

Or you can treat your Hour field like it's on a data island and do something like for Processed Orders.

Count(

     {$

          <Hora_Processado= {"={$(=Concat(Distinct Hour,','))"},'Date={"$(=Date(max(Date),'DD/MM/YYYY'))"}, _Id_Flag={0},Estatus_Cobranza= {'EN SURTIDO','FACTURADO','ENTREGADO','CONSOLIDADO'}, Liberado={1}, TipoDeEntrega -= {'SOLO FACTURA'}

     }

     distinct Numero_OrdenVenta)

And this for delivered.

Count(

     {$

          <Hora= {"={$(=Concat(Distinct Hour,','))"},'Date={"$(=Date(max(Date),'DD/MM/YYYY'))"}, _Id_Flag={0},Estatus_Cobranza= {'EN SURTIDO','FACTURADO','ENTREGADO','CONSOLIDADO'}, Liberado={1}, TipoDeEntrega -= {'SOLO FACTURA'}

     }

     distinct Numero_OrdenVenta)

[marygrace]

Hi Wallo,

I tried your suggestion, does not woks, I will read the information about canonical date.

[Anonymous]

You could try

Count(

     {$

          <Hora_Processado=P(Hour),'Date={"$(=Date(max(Date),'DD/MM/YYYY'))"}, _Id_Flag={0},Estatus_Cobranza= {'EN SURTIDO','FACTURADO','ENTREGADO','CONSOLIDADO'}, Liberado={1}, TipoDeEntrega -= {'SOLO FACTURA'}

     }

     distinct Numero_OrdenVenta)

[marygrace]

well,

Does not Works ;(