Hi, everybody.
Would you land me your hand, please?
The task:
I have week prompt. User selects week necessary but in first time I need to select current week by default.
Crosstab should show quarter the week selected belongs and 4 previous quarters (vertical). Horizontal all weeks of quarter and some fact for sure.
My realization:
# [current week] = tail(Package.Business Calendar.WS Calendar.Week,1)
# [Quarters] = lastPeriods(5,ancestor(#prompt('pWSWeekPrompt', 'memberuniquename', '[current week]', '','[Package].[Business Calendar].[WS Calendar].[Week]')#,2))
# [some fact] = drag&drop from fact dimension
# [weeks] = [Package].[Business Calendar].[WS Calendar].[Week]
My issues:
1) How to show current week in prompt in time of loading and use it in calculations?
I got error "CCL_ASSERT_NAMED(parametersAdded > 0, "Faulting on parameters that are already defined"); RSV-SRV-0042 " with initialization of default choice.
2) How to calculate average for prev 4 quarters and don't include current
Now I have item calculated
except(lastPeriods(5,ancestor(#prompt('pWSWeekProm pt', 'memberuniquename', '[current week]', '' ,'[Package].[Business Calendar].[WS Calendar].[Week]')#,2)), ancestor(#prompt('pWSWeekPrompt', 'memberuniquename', '[current week]', '' ,'[Package].[Business Calendar].[WS Calendar].[Week]')#,2))
then use simple Average and hide this items to keep only summary with aggregation function.
Any ideas how to solve that task or simplify maybe some way?