Turn a Calendar week number into dates

[Former Member]

Hello experts!

How can I display a date based on a calendar week?

My internal customer doesn't want to see a number (calendar week), he wants to see the data in week buckets but in dates.

Instead of seeing Week 35 (calendar week) he wants to see = 08.24.2015 to 08.30.2015

My query filter is based on relative week (from -1 to -6). so anytime the data in the report is pulled it will display last 6 weeks info.

Thank you,

[Former Member · Accepted solution]

If you have date dimension in universe then you can try following formula

Date_start = min[date] in([week]))

date_end = max([date] in ([week]))

Now you can use these two variable in report to show the date range for each week.

You have to drag the week object also for providing input context & then you can hide that week column.

Thanks,
SB

[Former Member · Accepted solution]

the only dimension that has date is  "delivery date"

so, I tried the formula:

Date-start= min ([Delivery date] in ([week ])) but I am not getting a value, I am getting just the text of the formula as the result.

[Former Member · Accepted solution]

You are getting text because you have not applied "=" in front of formula

try below formula

=min([Delivery date] in ([week ]))

Don't leave space between "=" and min

Thanks,

Swapnil

[Former Member · Accepted solution]

What dimension should be the [week] from the formula?

I tried using [calendar week] or just the word week but I am getting a multivalue.

Do I have to create a variable called week in order to use in the variable =min([Deliverydate] in ([week])) ?

[Former Member · Accepted solution]

Can you please do one thing...

Show me the snapshot of report containing only two objects

1) Calendar week

2) Delivery date

Rest I will check at my end and give you the final solution!

[Former Member · Accepted solution]

yes, I am sending the screen shot and I realized it is better to use GI Actual date as Calendar week is based on that date, not on Delivery date.

[Former Member · Accepted solution]

you can use following formula:

min_date_per_week =min([Gl Actual Date] in([Calendat week]))
max_date_per_week =max([Gl Actual Date] in([Calendat week]))

Please try following formula. make sure you are using calendar week object on report so that you won't get multivalue error.

Thanks,

Swapnil

[Former Member · Accepted solution]

I appreciate your time and support but I am still getting multivalue. The other weird thing is that I am creating the variable as a dimension but system is saving it as a measure.

Dimension "GI Actual date" is data type: date

"Calendar week" is data type: string

used formulas

=min([GI Actual Date] in ([Calendar week]) and kept calendar week in the report.

same thing for max date formula

[Former Member · Accepted solution]

Can you please provide me the snapshot where you are getting multivalue error on report.

It would help me to understand the issue.

Please provide whole report snapshot what you are creating for user.

[Former Member · Accepted solution]

sure, here are the screen shots.

[Former Member · Accepted solution]

Try following formula

=min([GI Actual Date] in ([Calendar week];[Gl Actual Date])) in ([Calendar Week])

Thanks,

Swapnil

[Former Member · Accepted solution]

Perfect it worked and I already concatenate both dates to show the week range date.

thank you so much!