Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jsanchezmayoral
New Contributor III

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
MVP
MVP

Re: Filtering within Quarter table set analysis

How about this

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

6 Replies
MVP
MVP

Re: Filtering within Quarter table set analysis

May be try this

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

jsanchezmayoral
New Contributor III

Re: Filtering within Quarter table set analysis

Hi Sunny,

What is MonthYear?. My qlik doesnt recognize it.

MVP
MVP

Re: Filtering within Quarter table set analysis

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

jsanchezmayoral
New Contributor III

Re: Filtering within Quarter table set analysis

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

MVP
MVP

Re: Filtering within Quarter table set analysis

How about this

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

jsanchezmayoral
New Contributor III

Re: Filtering within Quarter table set analysis

Thank you, that worked with a light modification!!

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