topic Set Analysis - Previous Season Week in QlikView

Set Analysis - Previous Season Week

Thu, 08 Sep 2011 14:55:24 GMT

I'm having trouble with a set analysis, its not returning the expected beginning inventory for the previous season week.

Here is what i'm using....

sum({$<fiscalseasonweek={'=(fiscalseasonweek)-1'}>}oh_dollars)

This is sample data for fiscal season week 1 and 2.

For fiscal season week 2, the BOP should be the EOP of the previous week.

fiscalseasonweek	BOP $	EOP $
1	0	408

408

6237

Instead, the expression is returning the same amount as in the EOP of week 2.

fiscalseasonweek

BOP $

EOP $

6237

Please Help.

Thanks,

Sara

Set Analysis - Previous Season Week

swuehl — Thu, 08 Sep 2011 15:56:29 GMT

Hi,

I think you can't use a set expression like this (season week is also your dimension, and you need the expression evaluated by row, but set expression is evaluated only once).

Maybe you could try a chart inter record function instead, like

=above(sum(oh_dollars))

Regards,

Stefan

Set Analysis - Previous Season Week

Thu, 08 Sep 2011 16:12:06 GMT

I dont have season week in my dimensions, it just used it for the example.

Set Analysis - Previous Season Week

swuehl — Thu, 08 Sep 2011 16:24:38 GMT

My point is that I think it is problematic to use your dimension in the set expression like this.

Have you tried the above() function?

Set Analysis - Previous Season Week

Thu, 08 Sep 2011 16:35:09 GMT

yes, i tried the above () function. However, it only works if i have season week as a dimension, but i dont want to include it in my pivot table. Furthermore, when i select on one season week as a filter, the value becomes null.

Set Analysis - Previous Season Week

swuehl — Thu, 08 Sep 2011 16:39:09 GMT

So, what are your dimensions, then?

If you want the sum to become selection insensitive, you could probably use

=above(sum({1} oh_dollars))

=above(sum({<fiscalseasonweek= >} oh_dollars))

Set Analysis - Previous Season Week

Thu, 08 Sep 2011 19:00:41 GMT

my dimensions are a merchandise hierarchy (for example, department and vendor).

I tried settling with above(sum({<fiscalseasonweek= >} oh_dollars)), and can maybe work with having fiscalseasonweek displayed as a dimension.

The problem now is that it lists all fiscal season weeks, even if i have only one selected,

Re: Set Analysis - Previous Season Week

swuehl — Thu, 08 Sep 2011 23:45:47 GMT

Maybe I am beginning to understand, you don't want a time series as dimension (fiscal week) in your table, but a point in time reporting for one column (figures for previous fiscal week), where you have selected one fiscal week as baseline, correct?

So maybe you are looking for something like:

sum({$<fiscalseasonweek={"=$(=max(fiscalseasonweek)-1)"}>} oh_dollars)

for BOP. (But then your EOP should be limited also to one selected month, shouldn't it? Or are you using "always one selected value" in list box properties?

Regards,

Stefan

Re: Set Analysis - Previous Season Week

Fri, 09 Sep 2011 12:33:45 GMT

Correct, i'm always using one selected balue as the fiscal week. i will try your suggestion.

Also, while working with this yesterday, i noticed that it worked better if a i set up a variable for fiscalseasonweek-1, and used the variable in my set analysis.

Re: Set Analysis - Previous Season Week

swuehl — Fri, 09 Sep 2011 12:39:43 GMT

Well, if you are using only one selected value, you don't need the max() function stuff, you could use only() function or probably just use your original expression (this might / should work if you get the format correct).

Maybe your alternative using a variable is already working (I don't think that's because of the variable, I assume your variable matches the required format and your current expression does not.

Anyway, keep trying and good luck.

Stefan