Dynamic parameters for week/year in SAP BW

[Anonymous]

Hello,

I have read about a solution,you find it below, but I still not manage to fix it.

If I change in the code below 1 into 10 then it will return week 10 (old example).

The soluation was that you can add as much weeks as you want but how ?

I for example want to add 52 weeks.

MyFunction is a written code that assures that the weeks has two digits

let
    ThisWeek = MyFunction(Date.From(DateTime.FixedLocalNow())), 
    NextWeek = MyFunction(Date.From(Date.AddWeeks(DateTime.FixedLocalNow(),1)))
in
    {ThisWeek,NextWeek}

[v-jingzhang]

Hi @Anonymous 

In your code, DateTime.FixedLocalNow() returns a datetime value set to the current date and time. Date.From() extracts the date value from the value inside. Date.AddWeeks returns the datetime result from adding numberOfWeeks weeks to the datetime value inside. When you insert 10, it returns the datetime which is 10 weeks later after the current datetime. This is correct. 

If it doesn't meet your requirement, what is the expected result you want to get?

Best Regards,
Community Support Team _ Jing

[Anonymous]

Hello v-jingzhang,

Thanxs for your reaction. I understand your explanation but what I want is not returning just one value in the Addsweek code but 52 weeks so I have the values of 52 weeks forwards.

[v-jingzhang]

Hi @Anonymous 

Sorry I misunderstood it. So here you need to generate a list for it. Try something like below. Replace "202101" and "202152" with your ThisWeek and NextWeek parameters.

List.Transform({202101..202152}, each "[0CALWEEK].["&Text.From(_)&"]")

Then use this list to replace the highlighted list in below code. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anonymous]

Hi v-jingzhang,

The code below doesn't give a syntax error, but if I close the editor this failure is popping up

Expression.Error: 1 arguments were passed to a function which expects 2.
Details:
Pattern=
Arguments=[List]

[v-jingzhang]

Hi @Anonymous 

Try replacing the whole line in Advanced editor with below one. MyFunction is the function you mentioned in your initial post. It should convert a date to the format as "202212" which combines year and week number. 

{Cube.ApplyParameter, "[!V000005]", List.Transform({ Number.From(MyFunction(Date.From(DateTime.FixedLocalNow()))) .. Number.From(MyFunction(Date.From(Date.AddWeeks(DateTime.FixedLocalNow(),10))))}, each "[0CALWEEK].["&Text.From(_)&"]"),

Jing

[Anonymous]

Hello Jing

edited as you recommended but the message is 

Expression.Error: 3 arguments were passed to a function which expects 2.
Details:
Pattern=
Arguments=[List]

[v-jingzhang]

Hi @Anonymous 

If you use only two dates on the row, just like in your original post, is it able to get data correctly? According to the error message it mentions "3 arguments were passed to a function which expects 2" but I'm not sure which function is it. 

[Anonymous]

Hello Jing,

If I use it in my original post I get the same result namely two weeks, this week and just one week in the future.

The funciton I use is :

let
Quelle = if
Number.RoundDown((Date.DayOfYear(dDate )-(Date.DayOfWeek(dDate , Day.Monday)+1)+10)/7)=0

then
Number.ToText(Date.Year(dDate)-1)&Number.ToText(Number.RoundDown((Date.DayOfYear(#date(Date.Year(dDate )-1,12,31))-(Date.DayOfWeek(#date(Date.Year(dDate )-1,12,31), Day.Monday)+1)+10)/7))

else if
(Number.RoundDown((Date.DayOfYear(dDate )-(Date.DayOfWeek(dDate , Day.Monday)+1)+10)/7)=53
and (Date.DayOfWeek(#date(Date.Year(dDate ),12,31), Day.Monday)+1<4))

then
Number.ToText(Date.Year(dDate)+1)&"01"

else
Number.ToText(Date.Year(dDate))&Number.ToText(Number.RoundDown((Date.DayOfYear(dDate)-(Date.DayOfWeek(dDate, Day.Monday)+1)+10)/7))
in
Quelle

Maybe this will help you

Regards,

Huib