Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello community, I come with a question that I hope you can help me.
I need to show the last value of a field for a date according to a dimension.
For example, I have these data:
The expected result should be this:
I have tried the following Set Analysis, but it doesn't work:
sum( {< Fecha= {"$(=Date(Max( Aggr( Max( Fecha), Cosecha)), 'YYYY-MM-DD'))"} >} TotalPrecioHecho)
The result of this is this:
I lose values where the date is not Max for the entire data set. The last loaded data is from the date "2020-06-10", but it is not for the 2017/2018 harvest ("Cosecha"), for that it is "2019-11-13".
Can you help me?
Sorry if my English is not very good, I am using Google Translate.
Thanks.
One script solution is.
tab1:
LOAD * INLINE [
Fecha, Cosecha, Producto, Total Comprado, Total Precio Hecho
6/10/2020, 2019/2020, Soja, 25309000, 13023000
6/3/2020, 2019/2020, Soja, 24453000, 12456000
11/13/2019, 2019/2020, Soja, 8222000, 4864000
11/13/2020, 2017/2018, Soja, 38816000, 25234000
11/6/2019, 2019/2020, Soja, 7786000, 4572000
11/6/2019, 2017/2018, Soja, 38798000, 25217000
];
Left Join(tab1)
LOAD Cosecha, Date(Max(Fecha)) As Max_Fecha
Resident tab1
Group By Cosecha;
Dimension: =If(Fecha=Max_Fecha,Fecha)
Output:
Front end solution:
Dimension: Cosecha
Expressions:
Date(Max(Fecha))
FirstSortedValue([Total Comprado],-Fecha)
FirstSortedValue([Total Precio Hecho],-Fecha)
Sorry, but not work.
The result was this:
Any idea why it doesn't work?
Please check if the FirstSortedValue return more than one value. In that case, the output will be NULL.