3 Replies Latest reply on Nov 29, 2017 7:32 PM by Zhouyi Zhang

# I want to have three date range in column field so that I can view my last sales of Latest 4 Weeks, Latest 12 Weeks and Latest 52 Weeks together

I want to see L4WK, L12WK and L52WK together. My concern is i was able to see all of them individually but when combined in a single statement i couldn't see values. Below is the calculation I used.

if datediff('week', [Date], today()) <=4 and datediff('week', [Date], today())>0 then "Last 4 Weeks"

elseif datediff('week', [Date], today()) <=12 and datediff('week', [Date], today())>0 then "Last 12 Weeks"

elseif datediff('week', [Date], today()) <=52 and datediff('week', [Date], today())>0 then "Last 52 Weeks"

else "Prior to Current Year" end

Please let me know if there is any workaround to achieve this.(I don't want this to be achieved using parameters due to some restrictions for adhoc analysis)

• ###### 1. Re: I want to have three date range in column field so that I can view my last sales of Latest 4 Weeks, Latest 12 Weeks and Latest 52 Weeks together

Hi,

You can't do it in this way unless you union your data set to it self 3 times.

And then modify your calculation as below

if datediff('week', [Date], today()) <=4 and datediff('week', [Date], today())>0 and [Table Name] = "your 1st table name" then "Last 4 Weeks"

elseif datediff('week', [Date], today()) <=12 and datediff('week', [Date], today())>0 and [Table Name] = "your 2nd table name" then "Last 12 Weeks"

elseif datediff('week', [Date], today()) <=52 and datediff('week', [Date], today())>0 and [Table Name] = "your 3rd table name" then "Last 52 Weeks"

end

ZZ

• ###### 2. Re: I want to have three date range in column field so that I can view my last sales of Latest 4 Weeks, Latest 12 Weeks and Latest 52 Weeks together

I appreciate it. But is there any other alternative other than union for 3 times? union will kill my performance as I have a huge data set.