Filtering within Quarter table set analysis

[Anonymous]

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.

[sunny_talwar]

May be try this

FirstSortedValue(Aggr(Sum(Valor), MonthYear), -MonthYear)

[Anonymous]

Hi Sunny,

What is MonthYear?. My qlik doesnt recognize it.

[sunny_talwar]

A field which you might need to create in the script... or it can be another field which contains year and month information

[Anonymous]

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

[sunny_talwar · Accepted solution]

How about this

FirstSortedValue(Aggr(Sum(valor), fecha, Trimestre), -Aggr(fecha, fecha, Trimestre))

[Anonymous]

Thank you, that worked with a light modification!!

FirstSortedValue(Aggr(Sum(valor), fecha),-Aggr(fecha, fecha))