How to combine Set Analysis and Aggr functions?

[#1]

Hello people.

I'd like to know if I'm trying to do something impossible in QlikView because it didn't work.

I want to select just the current (or other) month and year in my calendar. Once that the current month and year are selected I need to sum the sales from the current period and compare it with the same month but one year before. In other words what I want is to compare sales from Nov/2009 with Nov/2008.

The biggest problem here is not to get the values. It's how to combine Set Analysis and Aggr functions to show them by selecting only one month and year.

I know that I can get the correct values that I need by making a sum in the script, like I did in the attached file. But I have a bigger problem to solve with the same idea and it would be harder to solve it in the script. I'm sending an example to clarify my idea.

Thanks in advance.

Huberto P. Haidemann

[#2]

Hi Huberto,

Could my modification works for you?

Check out it and let me know your experience.

Best regards.

[#3]

Hi Miguel.

First of all, thanks. Your suggest works fine and I'm going to use it in my application.

To tell the truth, now I don't remember why I used that expression. But there was a mistake in the results.

Another way to improve your solution is this, without variables:

sum ( {$<Ano = {$(=max(Ano) - 1)}>} [Valor Liquido Mercadoria] / [Valor Cotacao Dolar Exportacao])

But I'd like to know if you could keep that structure and show me the same values. Or if it's not possible, could you try to explain me the reason why it didn't work?

Thanks.

Huberto.

[#4]

Hi Humberto,

First at all, you're wellcome.

About your doubt, I don't understand what do you wanna say with "if you could keep that structure and show me the same values", could you be a little bit more specific?

Best regards.

[#5]

Hi Miguel.

Sure, I will try to explain better why I need to keep that structure.

I have another application where I combine both functions, Set Analysis and Aggr. Your suggestion was excellent. It solved my problem but if I solve this problem in the current example I will be able to solve the problem in the other application. I guess.

This is the expression that I have in the other application:

count(
 aggr ( {$<Ano={"*"}, Mes={"*"}, Dia={"*"}> *
 1<Periodo = {">=$(#=date(addmonths(makedate(max(Ano), max(Mes), 1), -2), 'MMM-YYYY')) <=$(#=date(makedate(max(Ano), max(Mes), 1), 'MMM-YYYY'))"}>}
 if (count (distinct if ([Pedido Liberado] = 1, Pedido)) -
 count (distinct if ([Pedido Liberado] = 0, Pedido)) > 0, Periodo), LinkRepresentante, LinkLinha, Periodo))

In my opinion, the expression above is a little bit complex to explain briefly. So I'd like to solve that problem using a simple example.

If you didn't understand something that I wrote, please, let me know.

Thanks a lot Miguel.

Huberto.

[#6]

Hi Miguel.

I wonder if you have any news for me.

Thanks.

Huberto.

[#7]

Hi Humberto,

Sorry by my absence, I've been moving house, so I wasn't looking my email.

I've tried reproduce your case but I don't understand what is it's structure, so If you could send me a little example with data maybe I could help you.

I wonder about data in the field Periodo, it's format is MMM-YYYY? because your condition is based in >= Date('date', 'MMM-YYYY') and it's returning the shortname of the month concatenated with the number of year, ¿is this a correct comparison?. I mean, ¿Periodo is a date field as well?.

Best regards.

[#8]

Hi.

I'm sorry, Miguel. When I said "But I'd like to know if you could keep that structure and show me the same values." I mean if you could keep the original expression. The expression you can see in the file that was attached in the first post.

About the field Periodo you are right. The field contains a date with the format "MMM-YYYY".

The expression works fine when I don't use the function aggr. But, when I combine Set Analysis with aggr the data are showed only if I select the months.

What I need is to select just one month and one year and get the same values that I have when I select the months. In this example, there are three months to be selected.

I was talking to some friends here in Brazil and they told me that it doesn't work. But I'm not sure about that…

Thanks again.

Huberto.

[#9]

Hi Huberto,

I'm not understand why you want keep the original expression, because I found two reasons, the first one is the sentence is wrong, the intersection is wrong formulated and the second one you are doing an aggregation by a field named LinkNF and afterwards the graphic applies 3 fields dimensions.

So, I don't know what really do you want, but I understand do you wanna get a value independent the months selected, based on the year before to the selected only, if this is right, here you are a possible solution.

I hope this help you.

Best regards.

[#10]

Hi again.

I'm so sorry, Miguel. It was not exactly what I was looking for. I feel I'm not clear enough to make you understand me. Let me try to explain in another way.

In this application I know the structure (expression) was wrong. So, I corrected it as you had suggested and now it's OK. You were completely right about my mistake. But I'm using the same application to understand why I cannot combine the function aggr and Set Analysis.

The expression below is the second column in my example and it has the correct values. Why? Because Set Analysis ({$<Ano = {$(=max(Ano) - 1)}>}) returns the year 2008. So, the amount showed is related to Out/2008.

But give a look at the field [Valor Liquido Mercadoria US$]. This field comes from the script, the division was made in the script level.

sum ( {$<Ano = {$(=max(Ano) - 1)}>} [Valor Liquido Mercadoria US$])

The goal of the first column is to reproduce the same values that I get in second column. But there I'm combining Set Analysis and the function aggr to get the values. For my surprise the values refer to Out/2009 (third column) not Out/2008 like I hoped.

In this expression Set Analysis statement doesn't have any effect. The month is correct (Out) but the year should be 2008 not 2009 (the values are from 2009).

sum (
 aggr( {$<Ano = {*}> * 1<Ano = {$(=max(Ano) - 1)}>}
 [Valor Liquido Mercadoria] / [Valor Cotacao Dolar Exportacao], LinkNF))

Can I combine Set Analysis and the function aggr? Does it work?

Is there something wrong in the Set Analysis statement?

The key point I'm asking is why the first column doesn't return the same amount as the second column. When I combine Set Analysis and the function aggr the Set Analysis statement doesn't any effect on select the correct year (2008).

Thank you so much.

Huberto.