Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
marygrace
Contributor III
Contributor III

SUM a Value Distinct Orders Today + Yesterday

Hello,

I want to SUM the distict Orders Packeges Today and Yesterday, It already works for today not for yesterday.

Somebody can let me know what is wrong

//SUM Distinct Packeges Today------ is good

SUM(aggr(Sum(distinct{<Date={"$(=Date(max(Date),'DD/MM/YYYY'))"}, _Id_Flag={0},Estatus_Cobranza= {'EN SURTIDO','FACTURADO','ENTREGADO','CONSOLIDADO'}, Liberado={0}, 

       TipoDeEntrega -= {'SOLO FACTURA','NO INVENTARIABLE'},Año={$(vAñoActual)},Mes=,Semana=>}Cant_Cajas),Numero_OrdenVenta))

       

+

//SUM Distinct Packeges Yesterday----- is wrong

SUM(aggr(Sum(distinct{<Date={"$(=Date(max(Date)-1,'DD/MM/YYYY'))"}, _Id_Flag={0},Estatus_Cobranza= {'EN SURTIDO','FACTURADO','ENTREGADO','CONSOLIDADO'}, Liberado={0}, 

       TipoDeEntrega -= {'SOLO FACTURA','NO INVENTARIABLE'},Año={$(vAñoActual)},Mes=,Semana=>}Cant_Cajas),Numero_OrdenVenta))

       

Thank you!

1 Solution

Accepted Solutions
Anil_Babu_Samineni

Because, We need to degrade with the outer aggregation as well like filters as below

Sum({<[Fecha Liberado CyC]={'$(=Date(max([Fecha Liberado CyC])-1,'DD/MM/YYYY'))'},

Estatus_Cobranza= {'EN SURTIDO','FACTURADO','ENTREGADO','CONSOLIDADO'}, Liberado={0},

TipoDeEntrega -= {'SOLO FACTURA','NO INVENTARIABLE'}>}

Aggr(Sum(distinct{<[Fecha Liberado CyC]={'$(=Date(max([Fecha Liberado CyC])-1,'DD/MM/YYYY'))'},

Estatus_Cobranza= {'EN SURTIDO','FACTURADO','ENTREGADO','CONSOLIDADO'}, Liberado={0},

TipoDeEntrega -= {'SOLO FACTURA','NO INVENTARIABLE'}>}Cant_Cajas), Numero_Entrega))

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

47 Replies
swuehl
MVP
MVP

Can you be a bit more specific what is wrong with the second aggregation?

What do you expect to get and what do you get?

Have you already checked what the dollar sign expansion returns? Or tried to replace it with a literal '30/11/2017'?

To tell more, more details on your model and data would also be more than helpful.

Anil_Babu_Samineni

//SUM Distinct Packeges Yesterday----- is wrong

SUM(aggr(Sum(distinct{<Date={"$(=Date(max(Date)-1,'DD/MM/YYYY'))"}, _Id_Flag={0},Estatus_Cobranza= {'EN SURTIDO','FACTURADO','ENTREGADO','CONSOLIDADO'}, Liberado={0},

      TipoDeEntrega -= {'SOLO FACTURA','NO INVENTARIABLE'},Año={$(vAñoActual)},Mes=,Semana=>}Cant_Cajas),Numero_OrdenVenta))

Can you try this, Perhaps?

SUM(aggr(Sum(distinct{<Date={"$(=Date(max(Date-1),'DD/MM/YYYY'))"}, _Id_Flag={0},Estatus_Cobranza= {'EN SURTIDO','FACTURADO','ENTREGADO','CONSOLIDADO'}, Liberado={0},

      TipoDeEntrega -= {'SOLO FACTURA','NO INVENTARIABLE'},Año={$(vAñoActual)},Mes=,Semana=>}Cant_Cajas),Numero_OrdenVenta))

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
OmarBenSalem

I remember facing such a problem and if I do remember correctly, I have added the condition as this:

SUM({<Date={"$(=Date(max(Date-1),'DD/MM/YYYY'))"}>}aggr(Sum(distinct{<Date={"$(=Date(max(Date-1),'DD/MM/YYYY'))"}, _Id_Flag={0},Estatus_Cobranza= {'EN SURTIDO','FACTURADO','ENTREGADO','CONSOLIDADO'}, Liberado={0},

      TipoDeEntrega -= {'SOLO FACTURA','NO INVENTARIABLE'},Año={$(vAñoActual)},Mes=,Semana=>}Cant_Cajas),Numero_OrdenVenta))

marygrace
Contributor III
Contributor III
Author

The second aggregation I need to have the measurement for one pervious day, if you select ‘30/11/2017’ I need to return the measurements for ‘29/11/2017’.

The first aggregation is the measurement for today, it is return the meserment correct.

I need to SUM measurement TODAY + YESTERDAY always.

marygrace
Contributor III
Contributor III
Author

Return 0

marygrace
Contributor III
Contributor III
Author

Return 0

marygrace
Contributor III
Contributor III
Author

Return 0,

Most all the measurements that I need to calculate are one day before and selected date

Anil_Babu_Samineni

May be Previous date you don't have data points? Can you check the same? in text object with

Max(Date)-1 ?? Same with Max(Date-1) using Date to work. Then start the same with the condition.

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
marygrace
Contributor III
Contributor III
Author

Hi Anil,

I already did, I validated  deleting -1, but If I add -1 does not calculate the value.