1 2 3 4 Previous Next 54 Replies Latest reply on Oct 1, 2014 3:01 PM by Chandni Bhowmick Go to original post
• 45. Re: Rolling backlog calculation

Thank you so much Kettan and Santosh for helping

• 46. Re: Rolling backlog calculation

I f you don't mind can you please briefly explain line 4 to 9.

• 47. Re: Rolling backlog calculation

It's hereby done in the comment stamped as correct.

Thanks for the green star  :-)

• 48. Re: Rolling backlog calculation

Hi
Thanks for the explaination

The data was only for the year 2014. If I want to include issues over many years should my lookup table look like the image attached?

• 49. Re: Rolling backlog calculation

Yes.

• 50. Re: Rolling backlog calculation

Hi

SELECT *

FROM [data\$] d, [lookup\$] l

WHERE datevalue(d.[valid_from])<=l.[month end]

and iif(d.[valid until] is null, #1/1/2099#,datevalue(d.[valid until]))>=l.[month start]

and l.[month start]<=date()

The above code is working fine in the attached workbook(Issues over many years) but when I'm adding the code below an error is popping up[Syntax error in FROM clause].

and (

d.[valid until] is null or

not (year[d.[valid until])=year(l.[month start]) and month(d.[valid until])=month(l.[month start]))

)

• 51. Re: Rolling backlog calculation

Your query had a typing error in line 8 where year[ should be year(.

Here is a query where this "minor" error is corrected:

SELECT *
FROM [data\$] d, [lookup\$] l
WHERE datevalue(d.[valid_from])<=l.[month end]
and iif(d.[valid until] is null, #1/1/2099#,datevalue(d.[valid until]))>=l.[month start]
and l.[month start]<=date()
and (
d.[valid until] is null or
not (year(d.[valid until])=year(l.[month start]) and month(d.[valid until])=month(l.[month start]))
)

• 52. Re: Rolling backlog calculation

Hey

Sorry to bother you again. I am doing the same thing but in weeks.  The code is not taking the function week().

Select *

from [data\$]d, [week1\$] w

WHERE datevalue(d.[valid_from])<= w.[end_date]

and iif(d.[valid until] is null, #1/1/2099#,datevalue(d.[valid until]))>=w.[start_date]

and w.[start_date]<=date()

and (

d.[valid until] is null or

not (year(d.[valid until])=year(w.[start_date]) and week(d.[valid until])=week(w.[start_date]))

)

• 53. Re: Rolling backlog calculation

datepart("w", datefield) works on my computer (I tested it with Tableau 8.1):

Select *
from [data\$]d, [week1\$] w
WHERE datevalue(d.[valid_from])<= w.[end_date]
and iif(d.[valid until] is null, #1/1/2099#,datevalue(d.[valid until]))>=w.[start_date]
and w.[start_date]<=date()
and (
d.[valid until] is null or
not (year(d.[valid until])=year(w.[start_date]) and datepart("w", d.[valid until])=datepart("w", w.[start_date]))
)

• 54. Re: Rolling backlog calculation

Thank you

1 2 3 4 Previous Next