Skip to main content
Question

Dimension combining calendar week and year

  • September 9, 2019
  • 2 replies
  • 6 views
M
Level 1
Hello all, I wonder if there is a way of combining the CALENDAR_WEEK() format with the YEAR() format similar to the way the statement ROUND_MONTH does for exmaple. The problem I have with the calendar week as the dimension is that in case of a dataset that includes data from more than one year, the KPIs do always calculate the data of all years for each calendar week while I would like them do distinguish between the weeks of the particular years. Best regards, Maximilian

2 replies

B
Level 0
Hi Maximilian, You could use YEAR("_CEL_O2C_ACTIVITIES".EVENTTIME) || - || CASE WHEN CALENDAR_WEEK("_CEL_O2C_ACTIVITIES".EVENTTIME) < 10 THEN 0 || CALENDAR_WEEK("_CEL_O2C_ACTIVITIES".EVENTTIME) ELSE CALENDAR_WEEK("_CEL_O2C_ACTIVITIES".EVENTTIME) END as Dimension to also separate accordingly. Try this with the KPIs you want to see in the OLAP. Best regards, Benedict Lang
M
Level 1
Hi Benedict, Thanks a lot for your solution. After some small adjustments I could apply it to my problem. Best regards, Maximilian
Community Code of ConductAccessibility statement