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.
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.
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.
I don't have access to Tableau Desktop for 2-3 weeks and cannot therefore not easily help you now.
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?
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.
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.
IFNULL was an Tableau function.
I have changed the Jet SQL condition to IIF and ISNULL and hope that helps.
It's also not recognizing the functions Today() and datetrunc().
I changed today() to SYSDATE.
What do I change datetrunc to?
We are getting closer
I have changed line 5 to use sysdate.
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].
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.