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???

[Anonymous]

see attached file with both qays (valielist and data Island - Suggestion from swuehl)

PROD2 does not have a value in week 11 for REG2

[sunny_talwar]

May be this:

If(Valuelist('Week','Year')='Week' or Len(Trim(Valuelist('Week','Year'))) = 0,

  sum(Sales),

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

UPDATE:
I think the issue is that ValueList() becomes null for some reasons and the if goes directly to the false statement

[swuehl · Accepted solution]

Maybe like this

[Anonymous]

amazing, with Expression len(... )=0  I get only table with PROD2

( I have QV 12, my custo´mer have QV 11.20 SR 12)

In your upper table I miss the cumulated values (year) for PROD1 and PROD3 as they had been sold in Week 5 and 10; only for PROD2 the calculation works

[Anonymous]

your solution with if (Only(TOTAL<Dim> Dim)='WeekA',

works (at least with the small example).

I will test it next monday at my customer site

thanks

[sunny_talwar]

Here is another try

If(Valuelist('Week','Year')='Week' or (Len(Trim(Valuelist('Week','Year'))) = 0 and (ColumnNo(TOTAL) * Avg({1} 1)) <= Count({<Week>}DISTINCT TOTAL Week)),

  sum(Sales),

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