I need some advice regarding how to capture the latest week for which we have data in the facts table.
At the database level I can run a select statement to select the Max(transaction date) from facts table and get its associated Week from the Calendar Dimension table.
For eg: If the max(transaction date)=1st october,2003, then it's corresponding week is [2003week40]
Now, I want to HARDCODE this value in the CYTD_YA calculations because the current formula only returns the week for which the store was open For eg. If the store was closed during 2003week13, then the CTYD_YA will return only first 13 weeks of 2002 instead of first 40 weeks of 2002, as week40 was the last uploaded week for 2003
The code for CYTD_YA is as follows:
------------------------------------------
Sum(
PeriodsToDate([Calendar_Time].[Year],
ParallelPeriod([Calendar_Time].Year, 1,
Filter ([Calendar_Time].Week.members,Not isEmpty (([Product].[Brand].[All Brand], [Measures].Volume))
). Item (Filter ([Calendar_Time].Week.members,Not isEmpty (([Product].[Brand].[All Brand], [Measures].Volume))). Count - 1). Item(0)
)))
------------------------------------------
When the User selects the particular store form the dropdown, the CYTD changes w.r.t that store
Observation:
If a store doesn’t have sales data after week 13 2003, the above formula will calculate only the SUM(measures) for first 13 weeks of last year, since the 'Filter' returns a value like '2003w13' and the function 'parallelperiod' will thus go back exactly 1 year i.e. '2002w13'. Now the function 'Sum(PeriodsToDate('will Summate the measures only for the first 13 weeks of 2002
Requirement:
I need to make the largest week,Independent of the WHERE clause i.e. issrepective of any store, or any product, the week shuld still be week40
I guess I need to HARDCODE the largest week i.e. week40 as shown below
Sum(PeriodsToDate([Calendar_Time].[Year],
ParallelPeriod([Calendar_Time].Year, 1,[2003w40])))
I understand that Periodstodate and ParallelPeriod functions in this context will only work on the calendar_time dimension, and so can I create some other dimension and have a member of a level hardcoded with this value?
Please advise on the effective ways to solve this issue.
If additional dimensions,calculated members are required, please let me know
Thanks in Advance
At the database level I can run a select statement to select the Max(transaction date) from facts table and get its associated Week from the Calendar Dimension table.
For eg: If the max(transaction date)=1st october,2003, then it's corresponding week is [2003week40]
Now, I want to HARDCODE this value in the CYTD_YA calculations because the current formula only returns the week for which the store was open For eg. If the store was closed during 2003week13, then the CTYD_YA will return only first 13 weeks of 2002 instead of first 40 weeks of 2002, as week40 was the last uploaded week for 2003
The code for CYTD_YA is as follows:
------------------------------------------
Sum(
PeriodsToDate([Calendar_Time].[Year],
ParallelPeriod([Calendar_Time].Year, 1,
Filter ([Calendar_Time].Week.members,Not isEmpty (([Product].[Brand].[All Brand], [Measures].Volume))
). Item (Filter ([Calendar_Time].Week.members,Not isEmpty (([Product].[Brand].[All Brand], [Measures].Volume))). Count - 1). Item(0)
)))
------------------------------------------
When the User selects the particular store form the dropdown, the CYTD changes w.r.t that store
Observation:
If a store doesn’t have sales data after week 13 2003, the above formula will calculate only the SUM(measures) for first 13 weeks of last year, since the 'Filter' returns a value like '2003w13' and the function 'parallelperiod' will thus go back exactly 1 year i.e. '2002w13'. Now the function 'Sum(PeriodsToDate('will Summate the measures only for the first 13 weeks of 2002
Requirement:
I need to make the largest week,Independent of the WHERE clause i.e. issrepective of any store, or any product, the week shuld still be week40
I guess I need to HARDCODE the largest week i.e. week40 as shown below
Sum(PeriodsToDate([Calendar_Time].[Year],
ParallelPeriod([Calendar_Time].Year, 1,[2003w40])))
I understand that Periodstodate and ParallelPeriod functions in this context will only work on the calendar_time dimension, and so can I create some other dimension and have a member of a level hardcoded with this value?
Please advise on the effective ways to solve this issue.
If additional dimensions,calculated members are required, please let me know
Thanks in Advance