Valuelist unexpected behaviour

[Anonymous]

Hi Folks,

I defined a Pivot table with a calculated Dimension (a valuelist) and the behaviour is rather curious.

A short example:

2 Dimensions (Region and Product), 1 calculated Dimension (Valuelist('Week','Year')

one Expression: sum Sales

What I want to Display is Region vertical, Valuelist in upper horizontal and Product in lower horizontal

Expression is defined as: if Valuelist('Week','Year')='Week', sum(Sales), sum ({<Week={'<=$(=max(Week))'}Sales)

I want to Display sales of selected week and cumulative sales (from start of year to selected week)

Header 1Region	Header 2	Header 3	Header 4	Header 5
	Week	Week	Year	Year
	Product 1	Product 2	Product 1	Product 2
Region1	10	20	50	100
Region 2	100	15	100	75

Now what is curious:
Whenever a Region has no sales, the expression does not calculate the first condition (Week) but the second one

So by simply defining if Valuelist('Week','Year')='Week',1,2 I do see 2 when there is no sales for this product

In the above table Product1 is not sold in Region 2, so Week contains the same value as Year

I cannot upload a small sample as upload is forbidden at customer site

the formulas (just an example) above are working (as all data is correct for regions with sales)

Any idea???

[swuehl]

Have you also tried the same with a data island dimension created in the data model instead of ValueList()?

[Anonymous]

not yet, should it be a better approach than valuelist?

[Kushal_Chawda]

try pick and match

Pick( match(Valuelist('Week','Year'),'Week','Year'),

sum(Sales), sum ({<Week={'<=$(=max(Week))'}Sales))

[Anonymous]

now week data seems to be okay (Shows "-" when there is no data)

but year data is not correct, total shows correct value, but only 1 product shows

correct data while other products Show "-"

[Kushal_Chawda]

Would you be able to share sample?

[Anonymous]

I will prepare a sample and upload it.

it may take a while

what i noticed with your pick method is  that i got Detail values for year onlyvfor

the product which alslo had sales in the week.

the total was correct in both cases

[swuehl]

Have you already tried the data island? I think it may help with your issue:

[Anonymous]

I attach example with both ways (Valuelist and Data Island).Please have a look to PROD3. In week 11 there is no sales for REG2. In the Pivot table we see the value for the year

[Anonymous]

please see attached file

hope you find a solution