0

In T-SQL I can retrieve ISO week number using dateprat function:

 select datepart(iso_week, sysdatetime())

Is there an equivalent in MDX? I have troubles to find it. I can retrieve number of week in MDX e.g. like this:

with member Measures.Test as (Format(now(), "yyyy") + cstr(datepart("ww", now())))
select  Measures.Test on columns
from    MyCube

But how about iso_week? Thanks, Petr

Petr
  • 15
  • 1
  • 5
  • No idea. The normal way to do this is to make iso_week a column in your time dimension table. – Tab Alleman Mar 08 '16 at 13:53
  • Tab Alleman: It is there. But now I need to retrieve some data since the begining until current week. And there is no current week indicator in the dimension. Is there a way to retrieve current ISO week number without adding current ISO week indicator into the Date dimension? – Petr Mar 08 '16 at 14:07
  • @Petr make the hierarchy visible so that you can see it when surfing the cube – whytheq Mar 08 '16 at 16:07
  • @whytheq: the week hierarchy is visible, with correct iso week numbers. (There is no hierarchy Year - Week - Day though). I need to write a MDX query for a report, containing all weeks until the current ISO week. I struggle to find correct current ISO week member. Any ideas? – Petr Mar 09 '16 at 08:05
  • Is there an "ISO Week" attribute you said? – SouravA Mar 15 '16 at 08:52
  • @SouravA: yes, there is – Petr Mar 16 '16 at 16:50

2 Answers2

0

Referring the comment

I need to write a MDX query for a report, containing all weeks until the current ISO week

You can try the below:

with member 
Measures.CurrentWeek as cstr(datepart("ww", now()))

member 
WeekValue AS [Date].[Week].CURRENTMEMBER.MEMBER_VALUE

select  
Measures.dummymeasures 
ON 0,
[Date].[Week].[Week].MEMBERS HAVING WeekValue <= Measures.CurrentWeek 
ON 1
FROM [YourCube]
WHERE ([Date].[Year].&[2016])
SouravA
  • 5,147
  • 2
  • 24
  • 49
  • I do something similar. But the tricky part is this: datepart("ww", now()). It does not return current ISO week number. – Petr Mar 10 '16 at 14:26
0

Please try below:

WITH
MEMBER [Measures].[Iso_week] AS Datepart("ww",NOW()-WeekDay(NOW(),2)+4,2,2)
SELECT [Measures].[Iso_week] ON 0
FROM [Some_cube]

More help here: http://www.snb-vba.eu/VBA_ISO_weeknummer_en.html

s0pel
  • 16
  • 2
  • This looks very promising. Do you use this approach? Is the formula reliable? Thanks a lot! – Petr Mar 16 '16 at 16:55
  • I actually use this approach and I never had any problems with it. I have made some additional tests with the dates like `2016-01-03` and `2016-01-04` and I have compared with SQL's `DATEPART(ISO_WEEK, [DATE])` and it shows correct values. – s0pel Mar 17 '16 at 08:52