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

The best for me would be to wait (2 weeks) till I have access to a laptop with Tableau Desktop.

That said, I wonder if this query (line 1 to 5 from before plus an inserted line 2) works:

```SELECT d.*, l.*
,  dateadd('d', 1 - day(d.[VALID_INTIL]), d.[VALID_INTIL]) AS [month trunc]
FROM [Sheet 1\$] d, [lookup\$] l
WHERE d.[VALID_FROM] <= l.[month end]
and iif(d.[VALID_INTIL] is null, #1/1/2099#, d.[VALID_INTIL])>= l.[month start]
and l.[month start] <= date()

```

This will tell if the dateadd calculation is okay or not and thus lead us one step closer to completion.

• ###### 31. Re: Rolling backlog calculation

The code is not giving any error but the result still remains the same

• ###### 32. Re: Rolling backlog calculation

Hi

Hope you're doing great. I'm still kinda stuck with this. Let me know when you get access to Tableau Desktop.
Thank you so much.

Chandni

• ###### 33. Re: Rolling backlog calculation

Thanks for telling. I expect to have access again tomorrow and hope to take a look at your workbook in this week.

• ###### 34. Re: Rolling backlog calculation

Finally I am back home :-)

This query works:

```SELECT *
FROM [data\$] d, [lookup\$] l
WHERE d.[VALID_FROM] <= l.[month end]
and iif(d.[VALID_INTIL] is null, #1/1/2099#, d.[VALID_INTIL]) >= l.[month start]
and l.[month start] <= date()
and not (
dateadd("d", 1 - day(iif(d.[VALID_INTIL] is null, #1/1/2099#, d.[VALID_INTIL])), iif(d.[VALID_INTIL] is null, #1/1/2099#, d.[VALID_INTIL])) = l.[month start]
and d.[VALID_INTIL] < l.[month end]
)

```

The issue was that the formula in line 7 had zero tolerance for null dates.

Therefore I copied the iif in line 4 and pasted it twice to the dateadd function.

That helped.

.

• ###### 35. Re: Rolling backlog calculation

Hi

I was getting the same output but the output isn't correct. Please see the attached excel file for the expected output.

Thanks!

• ###### 36. Re: Rolling backlog calculation

The bug was not in the SQL code, but in the lookup data sheet.

Month Start should be the first of each month, but were all in January.

I have attached a new Tableau workbook (plus the corrected Excel file).

Hopefully everything works as it should now  :-)

• ###### 37. Re: Rolling backlog calculation

Hi

In Jan the total should be 27 since 27 issues were open in Jan. Similarly  the total for Feb and March should be 50 and 52 respectively( shown in the excel sheet attached previously). Why are the numbers different in your tableau workbook?

Thanks

• ###### 38. Re: Rolling backlog calculation

I have changed the formula back to basics:

```SELECT *
FROM [data\$] d, [lookup\$] l
WHERE d.[VALID_FROM] <= l.[month end]
and iif(d.[VALID_INTIL] is null, #1/1/2099#, d.[VALID_INTIL]) >= l.[month start]
and l.[month start] <= date()

```

The above mentioned query returns:

Jan 29 (27)

Feb 61 (50)

Mar 79 (52)

For starters, could you tell me what two (29-27=2) internal issue numbers are too much in Sheet 3 in attached workbook in January?

.

• ###### 39. Re: Rolling backlog calculation

I'm not sure I understand your question.

• ###### 40. Re: Rolling backlog calculation

Hi,

I was going through your post. I found it interesting. Kettan has given you the right solution.

As per your requirement I understand that you need to show number of backlog issues by month.

So to meet your requirement I have attached the modified workbook here. Hope this is the solution you were looking for.

Thanks.

Santosh

1 of 1 people found this helpful
• ###### 41. Re: Rolling backlog calculation

I'm not sure I understand your question.

What I meant is if you could go through Sheet 1 in attached workbook and share which internal issue numbers are wrongly included in January (28 vs 27), February (56 vs 50), and March (61 vs 52).

As for January, I think your result sheet is 28, not 27. It is Excel that calculates wrong in your sum formula by excluding adjacent rows or something like that.

As for February, I noticed that your result sheet excludes 6 internal issue numbers beginning in January which have no VALID_INTIL date. I assume these should be included because internal issue numbers having a VALID_INTIL date are included in your result sheet even if they begin in January.

As you noticed, my number of rows have changed again.

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

```

Line 1 to 4 is the basic technique explained in  The Cross Join Collection

The function datevalue removes the time part from VALID_FROM and VALID_INTIL.

I had to either truncate the time part or include time 23:59:59 in the lookup column month end.

I chose to truncate. If there are performance issues, the other option might perform better.

Line 4 uses IIF to give VALID_INTIL null values a needed dummy future date.

Line 5 ensures that these dummy future dates don't create future months.

Line 6 to 9 exclude issue numbers which are closed in the same month as the shown month.

Line 7 ensures that null end dates are not excluded.

In this regard, it is important to notice its usage of the or operator instead of the usual and.

Line 8 excludes issue numbers which are closed in the same month as the shown month.

Here it is worth to notice the not ( condition 1 and condition 2 ) structure which only excludes if both conditions are true.

.

• ###### 42. Re: Rolling backlog calculation

Welcome to the forum, Santosh, and thanks for helping.

• ###### 43. Re: Rolling backlog calculation

"As for February, I noticed that your result sheet excludes 6 internal issue numbers beginning in January which have no VALID_INTIL date. I assume these should be included because internal issue numbers having a VALID_INTIL date are included in your result sheet even if they begin in January."

You're right! I missed this point! Thank you so much!

• ###### 44. Re: Rolling backlog calculation

You are welcome :-)

1 2 3 4