Sum sales set analysis doesn't work properly.

marcel_olmo — Mon, 21 Mar 2011 15:34:51 GMT

Hey guys, I've been trying to do the following without any result :

In my data model I have Companies and registered sales.

I need to know the maximum sales month per year and company in a "Rolling Months Table", so I've solved this in my script doing a max(month) grouping by Year and Company, as you can see :

load

max(AsOfMonthDisplayNum) as AsOfMonthDisplayNumMax

resident AsOf group by AsOfYear,AsOfCompany;

If I do the calculation of the maximum sales month per year, is quite easy to do, as you can see :

where max Sales Month is calculated by :

max({$<AsOfCompany={"$(currentCompany)"},AsOfYear=>} AsOfMonthDisplayNumMax)

which means the maximum montth value for the selected company for all the years. Easy right?

As you can see, in this case there are two maximum month possible values, 12 (from 2005 to 2009) and 9 (only for 2010).

I need to calculate the maximum month sales amount per company selected, and I'm trying to calculate like this :

sum({$<AsOfCompany= {"$(currentCompany)"},AsOfYear= , AsOfMonthDisplayNum={"$(=max({$<AsOfCompany={"$(currentCompany)"},AsOfYear=>} AsOfMonthDisplayNumMax))"} >} [Amount])

This is my current result :

As you can see, there is no amount in 2010. Obviously this is not correct.

The problem is that inside the "sales amount expression" it calculates the maximum month is equal to 12 over all the years.

I don't understand why if I put the month expression outside the set analysis, works fine (maximum month value per 2010 is 9), but inside the set analysis expression of my sales amount it says the maximum month is 12.

Is there any way to solve this? Or maybe this is some kind of bug??

Many thanks in advance!!!

Sum sales set analysis doesn't work properly.

marcel_olmo — Wed, 23 Mar 2011 12:11:41 GMT

Could anybody tell me if my problem described before is a bug? Or maybe there is another better way to do that?

Many thanks in advance!!

topic Sum sales set analysis doesn't work properly. in QlikView

Sum sales set analysis doesn't work properly.

Sum sales set analysis doesn't work properly.