Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

marygrace
New Contributor II

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

Re: SUM a Value Distinct Orders Today + Yesterday

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))

Life is so rich, and we need to respect to the life !!!
47 Replies
MVP
MVP

Re: SUM a Value Distinct Orders Today + Yesterday

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.

Re: SUM a Value Distinct Orders Today + Yesterday

//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))

Life is so rich, and we need to respect to the life !!!
OmarBenSalem
Esteemed Contributor

Re: SUM a Value Distinct Orders Today + Yesterday

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
New Contributor II

Re: SUM a Value Distinct Orders Today + Yesterday

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
New Contributor II

Re: SUM a Value Distinct Orders Today + Yesterday

Return 0

marygrace
New Contributor II

Re: SUM a Value Distinct Orders Today + Yesterday

Return 0

marygrace
New Contributor II

Re: SUM a Value Distinct Orders Today + Yesterday

Return 0,

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

Re: SUM a Value Distinct Orders Today + Yesterday

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.

Life is so rich, and we need to respect to the life !!!
marygrace
New Contributor II

Re: SUM a Value Distinct Orders Today + Yesterday

Hi Anil,

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

Community Browser