Calculate sum for last week of the month using aggr and set analysis

[#1]

Hi All,

I have a calendar with Year, Month, SaleMonth, WeekID (based on YYYYNN where NN between 1-52) and SaleMonth is MM/YYYY. Foreach WeekID I have multi dimension sales data such as UPC, Store, Sales. So for each UPC I have multiple stores with sales. I would like to first find the last WeekID for each of the months. This I was able to do using =aggr(max(WeekID), SaleMonth) as my dimension in straight table and using =(FirstSortedValue(WeekID,-WeekID)). Now I would like to calculate the sum of Sales for that given weekID.

I tried

=sum({$<WeekID = {"$(=(FirstSortedValue(num(WeekID),-WeekID)))"}> Sales)

without any success.

What is the underlying problem and how to solve it.

Cheers,

G.

[fosuzuki]

Hi G,

First of all, a general rule: try to avoid calculated dimensions since they will degrade the QV performance. Most of the times (but not always..) the problem can be solved by migrating the calculation to the script.

So, I think (if I understood it right... ) that you could calculate the 'last weekid for each month' in the script.

In your script, create a boolean field (lets say FlagLastWeekMonth, which will have 0s or 1s) to indicate for each WeekID if it is the last week for its month.

Next, in the layout, all you have to do is use the WeekID field as dimension and the following expression:

sum(Sales * FlagLastWeekMonth)

by doing this, QV will return non-zero values only for the WeekIDs which have the FlagLastWeekMonth = 1.

Hope this points you to the right direction.

Regards,

Fernando

[#3]

Thank you Fernando. Sounds easy enough. I will attempt implementing your suggestion. I agree that complext calculations are better left to the script.

Cheers,

G