Set Analysis - Previous Season Week

[#1]

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

2

408

6237

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

fiscalseasonweek

BOP $

EOP $

2

6237

6237

Please Help.

Thanks,

Sara

[swuehl]

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

[#3]

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

[swuehl]

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?

[#5]

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.

[swuehl]

So, what are your dimensions, then?

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

=above(sum({1} oh_dollars))

or

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

[#7]

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,

[swuehl]

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

[#9]

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.

[swuehl]

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