Problem with set analysis

[pepe2209]

Hello All,

I am fairly new to the set analysis method. And i am trying to replace some heavy if expressions with set analysis to increase the performance. Now i have a table with dimension "Company" consisting of ten companies.

In the expression I am trying to achieve this:

sum({$<isoweek = {">$(=WeekStart(Max_Week,-4))"}>} Sales)

so a sum of sales for the last 4 weeks counting back from "Max_Week".

The problem lies with the field "Max_Week". The set analysis expression works fine as long as "Max_Week" is the same for all the companies (for example all "2018/12"). But when one company has a different value for MaxWeek the expression turns out empty.

I'm guessing i need to integrate AGGR and/or ONLY into the expression or something, but i haven't been able to work it out.

Your help would be greatly appreciated.

Best regards,

Peter

[sunny_talwar]

Is Max_Week a variable or is that a field calculated in the script?

[pepe2209]

It is a field calculated in the script

[sunny_talwar]

May be try this

Sum(Aggr(If(isoweek > WeekStart(Max_Week,-4), Sales), Company, isoweek))

[pepe2209]

Thanks for your reply.

This expression does work. But now you are using an if statement and no set analysis. The goal is to get a better performance in calcultation time. The original expression i had was:

Sum(If(isoweek > WeekStart(Max_Week,-4), Sales)

You gave me this expression.
Sum(Aggr(If(isoweek > WeekStart(Max_Week,-4), Sales), Company, isoweek))

Does your version have a better performance?

[sunny_talwar]

No, it won't... the problem is that each company can have a differnt Max_week which won't really work within set analysis.... what you can do is to calculate a flag in the script

LOAD ...,

     If(isoweek > WeekStart(Max_Week,-4), 1, 0) as Flag

and then this

Sum({<Flag = {1}>}Sales)

or

Sum(Sales * Flag)