Question:
Hello experts.
In our cube, we have key figures in weekly time buckets. The exception aggregation of the key figure is Last Value (with Aggregate Ref Char 0Calweek).
During querying, when the calweek is drilled down, the report is behaving properly such that the result of the weeks belonging to the same month is equal to the value of the last week for that period. However, whenever we remove the drilldown for Calweek and use the characteristic Calmonth, the report adds the values for all the weeks. If we want to display at the Calmonth level but the value should be the last week of that month, is there a way to do it?
To illustrate:
Suppose Wks 1, 2, 3 and 4 belong to Month A.
Wk 1 key figure value = 10
Wk 2 key figure value = 5
Wk 3 key figure value = 20
Wk 4 key figure value = 15
Query A:
If we dispplay Month A and Wks 1,2, 3, 4, the query calculates the result as 15 for Month A (which is what we want)
Query B:
Displaying only month and not the weeks, we get a value of 50 for Month A.
How could we make Query B pick up the last value for month A (which is 15) without displaying the weeks?
Your suggestions/advice would be greatly appreciated.
Thanks in advance.
Ron
Answer:
not sure, as my answer look silly, but as nobody else tried...
use query A and set the weeks as 'no display' ?
Ch
_________________
_
There are only 10 types of people in the world :
those who understand binary and those who don't.
Answer:
Ron,
Did you ever get a resolution? We are having the same issue.
Thanks, Peggy
Answer:
Hi,
Do you have calendar day as reference characteristic for time aggregation in you Cube? (Should have)
Regards,
TVI
Answer:
No, we really wanted our ODS to be at Day level and our cube to be at week. I think we are finding that we "have" to have it at day level since 0CALWEEK is based on 0CALDAY.
Is that what you found?
Thanks!!
Answer:
We tried having and ODS by 0CALDAY and a Cube by 0CALWEEK without any exception aggregation for the key figures, just normal ones.
From BEx site we created a Calculated Key Figure and used option enhanced -> exception aggregation -> last value -> 0CALWEEK and it brings the last value for all time reference except 0CALWEEK.
On the other hand we created an ODS by 0CALDAY. A second ODS by 0CALWEEK and then an InfoCube by 0CALWEEK. The ODS by 0CALWEEK is the one feeding the cube.
The key figure that we used for the first ODS are normal ones but for the second ODS and cube the KF are with an exception aggregation of last value for 0CALWEEK. In this case the last value option works perfect for all time reference.
Do you know what can we do so we don't have to use two ODS?
Thanks,
Peggy.
Answer:
Hi Peggy,
When the day-details of the ODS are loaded to the cube they are assigned to the correct calweek, and...aggregated and stored to this level. So, no wonder the query can't find the last day-value.
Regards,
TVI