topic Problem with set analysis in QlikView

Problem with set analysis

pepe2209 — Fri, 23 Mar 2018 14:16:16 GMT

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

Re: Problem with set analysis

sunny_talwar — Fri, 23 Mar 2018 14:26:02 GMT

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

Re: Problem with set analysis

pepe2209 — Fri, 23 Mar 2018 14:43:46 GMT

It is a field calculated in the script

Re: Problem with set analysis

sunny_talwar — Fri, 23 Mar 2018 14:46:49 GMT

May be try this

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

Re: Problem with set analysis

pepe2209 — Fri, 23 Mar 2018 15:31:03 GMT

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?

Re: Problem with set analysis

sunny_talwar — Fri, 23 Mar 2018 15:35:04 GMT

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)

Sum(Sales * Flag)