Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have this table
Dimension "trimestre" are quarters with this formula: =[fecha.autoCalendar.YearQuarter]
Measures "Nuevos" and "Resueltos" are SUM functions of the values among the three months in that quarter.
My raw data have this format:
ID | fecha | Valor |
---|---|---|
1 | 01/12/2017 | 1 |
1 | 01/01/2018 | 2 |
2 | 01/01/2018 | 4 |
My problem comes at the last measure "Stock". In this case I want a SUM function just with the values belonging to the last month of that quarter.
I tried the following but it only works for the last quarter (as you can see in the table):
Stock --> =Sum({<fecha={"$(=max([fecha]))"}>}valor)
I want the last "fecha" (date) of that very quarter, not the absolut one.
Thanks in advance.
How about this
FirstSortedValue(Aggr(Sum(valor), fecha, Trimestre), -Aggr(fecha, fecha, Trimestre))
May be try this
FirstSortedValue(Aggr(Sum(Valor), MonthYear), -MonthYear)
Hi Sunny,
What is MonthYear?. My qlik doesnt recognize it.
A field which you might need to create in the script... or it can be another field which contains year and month information
Since my date field (fecha) only contains dates with the first day of the month, I guess I can sort it by date field in this way:
FirstSortedValue(Aggr(Sum(valor), fecha), -fecha)
But this is not working
How about this
FirstSortedValue(Aggr(Sum(valor), fecha, Trimestre), -Aggr(fecha, fecha, Trimestre))
Thank you, that worked with a light modification!!
FirstSortedValue(Aggr(Sum(valor), fecha),-Aggr(fecha, fecha))