Aggregation not working

[AidaAR]

Hi community,

I am trying to do an aggregation of a calculated field in my app but it does not work properly. The calculated field does work as it gives a value for each item but when trying to sum it, it gives always a 0 as a result. 

The calculated field is called PERDTH TOTAL whereas the aggregation dimension is TOTAL PERD TH:

TOTAL PERD TH: SUM(AGGR([PERDTH TOTAL],[AñoMes]))

I've tried changing the aggregation field but that does not work either.

If you have any idea as to why it does not work please help me. I thought I understood the aggregation function... 😞

[TauseefKhan]

Hi @AidaAR,

Check this:
Sum(Aggr(Sum([PERDTH TOTAL]), [AñoMes]))

[gomeri]

HI @AidaAR, formula given by @TauseefKhan is working.

If you create a Table where in first column you have dimension "PERDTH TOTAL" and in second column measure "=SUM(Aggr(SUM([PERDTH TOTAL]), AñoMes))" the result will be follow (i my case i change dimension "PERDTH TOTAL" in "VALORE" and "AñoMes" in "DATA":

Let us know if it's working

Thanks

[AidaAR]

Hi, no still not working. The first table in the image is the calculation of the PERDTH TOTAL by item and as you can see it gives a value per each item. The second is the aggregation of that value and it gives 0...

the formulation of the field PERDTH TOTAL is:

PERDTH TOTAL=NUM(COALESCE(0.01*[RMTO PESO LC]+ [RMTO PERDTH CONO]+[RMTO PERDTH CARRETE]+[RMTO PERDTH COLAS]+IF([RMTO REBORDEO]='S',7.85*[RMTO ESP RF]*[INS. Largo Minimo]/1000*([PRO Ancho anterior]-[RMTO ANCHO RF]),0),0),'0,00')

And the aggregation I put it as you said:

TOTAL PERD TH MES=Sum(Aggr(Sum([PERDTH TOTAL]), [AñoMes]))

[gomeri]

To solve this problem, you need to create a new variable or in "Editor Script" or in "Variables" of Sheet Editor.

In first case, you need to go into "Data Load Editor" and create:

- a new variable named "PERDTH TOTAL":

Let PERDTH TOTAL=NUM(COALESCE(0.01*[RMTO PESO LC]+ [RMTO PERDTH CONO]+[RMTO PERDTH CARRETE]+[RMTO PERDTH COLAS]+IF([RMTO REBORDEO]='S',7.85*[RMTO ESP RF]*[INS. Largo Minimo]/1000*([PRO Ancho anterior]-[RMTO ANCHO RF]),0),0),'0,00')

In second case, you need to go into from sheet to Variables and create new variable called "PERDTH TOTAL" with same definition. 

once the variable has been created, it is possible to insert the newly created variable within the second expression

Sum(Aggr(Sum($(PERDTH TOTAL)), [AñoMes]))

[AidaAR]

Good morning,

Still not working... Might it be because the calculation of PERDTH TOTAL is done with data from different tables? It shouldn't  be a problem but I don't know what else could be...

[gomeri]

Good morning, 

I wouldn't know how else to help you. I would advise you at this point to check the data model and see if it is possible to pre-calculate within the logical data model.

In case you manage to solve the problem yourself, let me know how you did it.

Thanks