how to choose last month in field

[francis_gr]

Hello!!

I have made a bar chart using 3 dimensions

=If([Evolucion Profesional]='P+' or [Evolucion Profesional]='P-','Ev.Prof.',(If([Evolucion Profesional]='CO' or [Evolucion Profesional]='CP','Cmbo.Destino',(If([Evolucion Profesional]='A' or [Evolucion Profesional]='B','Rotacion Externa')))))

=If(Year([Fecha Inicio])>=Year(Ini_Movilidad) and (Year([Fecha Fin])<=Year(FechaInforme) or IsNull([Fecha Fin])),Año)

Evolucion Profesional

and 1 expression

=Sum(If(mes)=max(mes),1,0)

that because i have 2 fields mes and year. until year=2010, I have only one value for mes, (mes=12), but from 2010 I have 8 months (january.....August) and for 2010 I only want the values of field Evolucion Profesional from last month., but my expression must be wrong.

Thanks for your help

[#2]

You have an error in your expression syntax. The If function is wrong. You need:

=Sum(If(mes=max(mes),1,0))

I don't know if that solves the problem, but it should get you past the first error. I think you're going to run into a problem using a Max inside a Sum. Nested aggregates cannot be used without the Aggr function. You may be able to create a variable to store Max(Mes) and then use a dollar sign expansion, but that would only evaluate Max(Mes) on the entire document, not record by record.

If you need the Max(Mes) to calculate separately for each record, you're going to need to use the Aggr() function.

[francis_gr]

Thaks NMIller for reply!

I'm neweste in qlikview, so Can you show me how i must use Aggr() funcion to create my expresion??

thanks

[#4]

It's kind of specific to the dataset and the chart in question. You need to first identify which dimensions you would like to aggregate on. Using the dimensions in your chart is usually a good start.

Then, I think you would want something like:

Sum(Aggr(If(Mes=Max(Mes), 1, 0), Dimension1, Dimension2, etc))

It may also be:

Sum(If(Mes=Aggr(Max(Mes), Dimension1, Dimension2), 1, 0))

Replace Dimension1 and Dimension2 with the dimensions you want to aggregate by. It can be one or more, just keep adding parameters. You can try out those two, but it is difficult to give an exact answer without knowing what your data looks like.

[francis_gr]

Hi NMiller.

I have cheked your instructions, but the chart shows "no data to show", so i wanna show you what's my start point and what I have tried until now

That's a example of my data origen (access). that's only for one employee. (850 in total)

Check that for years 2005-2009 i hve only one value for mes field (december) but for 2010 I have a value for each month. In my chart, for 2010 I need to count Evolucion Profesional values in the last month (april in the example)

Hope you can help me to find a solution.

Thanks again

[#6]

One of the hardest parts of using Aggr is just getting it to return something, let alone the right answer.

First, I would simplify things a bit. You're going right for the final result and have quite a few different things going on which complicates matters. My first step would be to create a chart with Evolucion Profesional as the dimension. Then add the three expressions to that and see if you get anything. The first one shouldn't work because of the embedded aggregates, so it's not necessary to try that one. You can also try just adding the Aggr function as an expression:

Aggr(Max(Mes), [Evolucion Profesional])

The key here is not to get the final answer you are looking for, but rather to find a working Aggr function.

Also, if you are working in a chart, you should probably temporarily switch to a straight table chart (use quick change). This will make it easier to determine what values are being returned. Once you have it working in a straight table, then you can switch back to your desired chart type.

I'll see if I can put something together using your sample data.

EDIT: Can you try this expression:

TextCount(Aggr(If(mes = Max(mes), [Evolucion Profesional]),
[Evolucion Profesional], Año))

You will need to add Año as a dimension parameter in your Aggr function, because you want the max month per Evolucion Profesional per Año.

You also have some issues with your calculated dimensions. One is that you are using variables without a dollar sign expansion. I couldn't get the second dimension to work in a straight table chart at all. The above expression seemed to work when using your first dimension above and simply Año as the second dimension along with Evolucion Profesional for the third.

I've attached a sample.

[francis_gr]

Hi!!

I have followed your indications but no way the max funcion woks as i need, so I've attached a sample to this post.

The char is what I have done right now. As you can see, there for 2009, the results are fine but in 2010, the bar accumulates the values from january to the last month (april in this example) that i have loaded from my access table, thats wrong because it would only have to show the last month.

Thanks!!

[#8 · Accepted solution]

Ok, that helps a bit. I missed a piece in the Aggr function, maybe this will help you get to the final result.

First, I turned your chart into a Straight Table to get a better idea of what was happening. I realized the Aggr wasn't working correctly in your chart, because of the dimensions. It was aggregating over your fields, but only within the context of the current dimension. In order to separate from the chart dimensions, you need to use the TOTAL modifier.

Using the following should get your the proper Max mes value on every line of your chart:

Aggr(Max(TOTAL <Año, [Evolucion Profesional]> mes), Año, [Evolucion Profesional], mes)

Then I can create an expression to only count the records in the last month of the respective year, but I'm getting a 1 for every one of those values instead of the count of instances. Here is that expression:

Count(If(mes = Aggr(Max(TOTAL
 <Año, [Evolucion Profesional]> mes), Año, [Evolucion Profesional], mes),
 [Evolucion Profesional]))

I've attached a sample.