Nested Aggregation problem

[Gabo77]

Hi guys.

Let me see if i can explain my problem.

This is my Data Model

Sesion: This table stores info about "Work sesions" that involves a production activities. FechaInicio field stores the Date and Time record when that sesions begin. FlagTipo field indicates if the record stored in table becomes from Production data or Plan data ("Plan data" indicates the time (TiempoPlan), quantity (CantidadPlan) and tons (TonsPlan) for each Celda, Date, Shift and Product)

Detalle: This table store info about each product that was worked for each sesion. "Tipo" field indicates if the Detalle record was about Good (B), Bad (M), Reworked (R) or Segregated (S) piece. FlagDetalle field is a counter with only a 1 value, for a use on graphic formulas.

Fechas: This table is a grouping table. Allows me to identify Sesion records for Anual, Mensual, Weekly and Daily groups. i.e. All records for 2023 year, or all records for August.  This is  data is stored on the table.

Products and Celdas table stores info for complementary catalogs of each element.

I need to calculate the goal for each sesion, defined as: "Percent of Tons by Hour produced vs Tons by Hour planned" for each Celda, Fecha (Date), Turno (Shift) and Product. This value was achive using next formula.

(Sum({<TonsPlan={">0"}>} (Netwgt / 1000)) * Count({<Tipo={'B','R'}>} FlagDetalle) / Sum(If(ProductId = 'SETUP', 0, TiempoPlan / 60))) / (Sum(TonsPlan) / Sum(If(ProductId = 'SETUP', 0, TiempoPlan / 60)))

NOTES:

1. TonsPlan > 0 indicates that theres a planned production for Product, on each Celda for a date and shift. This is because some times, our guys produces a sesion for a products not in a Production Plan. Those records must be ignored.

2. We only need to calculate production for a products identified as Good (B) or Reworked (R), because Bad (M) or Segregated (S) are related to discarded pieces.

3. Production Plan identified as ProductId = 'SETUP' was talking about a time for machine setup, not about a production, so that record needs to be ignored for a Plan calculation.

This is how this data are presented using this formula on "Cumplimiento" field.

The first thing that strikes me is the "Total"values... i suppose that is because is a SUM value but, if i changed for an Average option looks like this:

Looks bad but, i decide to continue.

Next, i need to obtain an average value for each group in "Fechas" Table... i mean... So far, what we have obtained, was a values for each Celda, Date by Date, and its goals... i need to obtain an Avg value grouped by Year, Month, Week and Day... something like this.

I use this formula to obtain "Cumplimiento" value

Avg(Aggr((Sum({<TonsPlan={">0"}>} (Netwgt / 1000)) * Count({<Tipo={'B','R'}>} FlagDetalle) / Sum(If(ProductId = 'SETUP', 0, TiempoPlan / 60))) / (Sum(TonsPlan) / Sum(If(ProductId = 'SETUP', 0, TiempoPlan / 60))), TipoPeriodo, Periodo))

I really believe that there's something i doing wrong... because my logic tells me that i need to aggregate by IdCelda, Fecha, Turno, ProductId and then use Avg funtion aggreged by TipoPeriodo and Periodo fields, but i cant realize where to do that... 😞

Can you help me??

Regards!

[Oleg_Troyansky · Accepted solution]

Hi Gabo,

I admit, your problem is very detailed and to understand it to the fullest, I'd need to spend a lot more time that I can devote to it, however, your last question appears to be rather simple. If you believe that you "need to aggregate by IdCelda, Fecha, Turno, ProductId and then use Avg funtion aggreged by TipoPeriodo and Periodo fields", then you do just that:

- Your AGGR function should have all the listed dimensions: IdCelda, Fecha, Turno, ProductId, but also TipoPeriodo and Periodo:

Avg(Aggr((Sum({<TonsPlan={">0"}>} (Netwgt / 1000)) * Count({<Tipo={'B','R'}>} FlagDetalle) / Sum(If(ProductId = 'SETUP', 0, TiempoPlan / 60))) / (Sum(TonsPlan) / Sum(If(ProductId = 'SETUP', 0, TiempoPlan / 60))), 
IdCelda, Fecha, Turno, ProductId,TipoPeriodo, Periodo))

- The outer aggregation is driven by the chart dimensions (TipoPeriodo and Periodo). So, you don't need to nest aggregations for this chart.

- If you needed an average value for all periods, then you can enclose the formula listed above in another AGGR() with these two dimensions only - TipoPeriodo and Periodo.

To learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin! Among many other advanced methodologies, I'm teaching Advanced Set Analysis and AGGR() there, which includes solutions like this one.

[Gabo77]

Hi Oleg... Thanks for responding...

Sometime, the answer is slapping at face... and even then, we can't see it 😞

I test the solution but... i believe there's something i doing wrong... because some values are greater than they must be...

This is the formula

Avg(Aggr((Sum({<TonsPlan={">0"}>} (Netwgt / 1000)) * Count({<Tipo={'B','R'}>} FlagDetalle) / Sum($(vHrsProd))) / (Sum(TonsPlan) / Sum($(vHrsProd))), IdCelda, Fecha, Turno, ProductId, TipoPeriodo, Periodo))

Does this makes sense to you??

Regards!

[Gabo77]

Please... Nevermind... this issue was about my Data quality... Thanks!!