Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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???
Maybe like this
Have you also tried the same with a data island dimension created in the data model instead of ValueList()?
not yet, should it be a better approach than valuelist?
try pick and match
Pick( match(Valuelist('Week','Year'),'Week','Year'),
sum(Sales), sum ({<Week={'<=$(=max(Week))'}Sales))
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 "-"
Would you be able to share sample?
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
Have you already tried the data island? I think it may help with your issue:
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
please see attached file
hope you find a solution