Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Filtering within Quarter table set analysis

Hi,

I have this table

tabla.PNG

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:

IDfechaValor
101/12/20171
101/01/20182
201/01/20184

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.

1 Solution

Accepted Solutions
sunny_talwar

How about this

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

View solution in original post

6 Replies
sunny_talwar

May be try this

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

Anonymous
Not applicable
Author

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
Not applicable
Author

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

How about this

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

Anonymous
Not applicable
Author

Thank you, that worked with a light modification!!

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