1 2 3 4 Previous Next 54 Replies Latest reply on Oct 1, 2014 3:01 PM by Chandni Bhowmick

# Rolling backlog calculation

Hi

I have a database of case data. Each record has a case opened and case closed date(Attached is a sample data set in excel).

I need to create a backlog report.

The backlog requirement is the following:

To be able to produce a number that tells me how many issues are in the backlog at the end of a given month(The values under the months in the excel file shows what the output should look like).

I am using oracle as database.

How do I carry this out in tableau?

Thanks!

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

Chandni, I can't open your Excel file (issue on my end). If you will connect to it in Tableau and then post a packaged workbook, I'll take a look.

--Shawn

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

Technique #1 in  The Cross Join Collection  might by one you can use.

You might also find a viable solution in a previous thread about the same issue.

See a collection of these in  The specified item was not found.

I cannot help in detail because my only access is from an iPad which doesn't have Tableau Desktop.

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

I used the cross join technique and also followed this example ' Rolling Backlog Calculations'.

I am attaching a packaged workbook. For the first issue the values should have been present only for jan and feb since the issue opens on 01/06/2014 and closes on 03/04/2014. However, its showing me the values under all the months. Similar is the case for the rest of the issue numbers.

I've also attached the excel file.

Can you help me with this.

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

That said, I think you need to adjust the query condition so it excludes the closing month.

Can you share your cross join query so it is easier for me to see what needs to be changed?

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

SELECT *

FROM [Sheet 1\$] d, [lookup\$] l

WHERE d.[VALID_FROM] <= l.[month end]

and d.[VALID_INTIL] >= l.[month start]

It is not taking the closing month into account. However, if an issue is opened on the last date of the month,it is added to the next month instead of that month and the newly opened issues that don't have a closing date is not being counted.

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

Here is a query which I believe does what you want except dealing with the issue of cases opened on the last day not appearing in that month. My suspicion is that this is because your date fields are datetime whereas the cross join logic assumes they are date without time. Can you change your datetime fields to date and see if it helps?

```SELECT *
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()
and not (
dateadd("d", 1 - day(d.[VALID_INTIL]), d.[VALID_INTIL]) = l.[month start]
and d.[VALID_INTIL] < l.[month end]
)

```

Line 4 and 5 deal with open cases, that is, those without closing date.

Line 4 uses a dummy future date whereas line 5 ensures that dummy future periods are excluded.

Line 6 to 9 exclude closing month.

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

Thank you so much.
It's giving the error below :

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

IFNULL was an Tableau function.

I have changed the Jet SQL condition to IIF and ISNULL and hope that helps.

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

Thank You!
It's also not recognizing the functions Today() and datetrunc().
I changed today() to SYSDATE.
What do I change datetrunc to?

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

We are getting closer

TODAY

I have changed line 5 to use sysdate.

DATETRUNC

Line 7 is changed to use the DATEADD and DAY functions to find start of month.

Line 7 [month end] is changed to [month start].

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

Thank you so much for your time!

I'm getting this error:

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

I changed "d" to 'd'.

If it doesn't help, it would be good if you isolated the line that returns the error.

The first step is to remove line 6 to 9 to ensure line 1 to 5 is okay.

1 2 3 4 Previous Next