Which of the non-overlapped (or at least the same value) totals is correct?
If you set the Fiscal Calendar to Month, then the values are correct for "window_sum(sum([Sales])) along Date". However, the same values are displayed when you set the Fiscal Calendar to Week, which is incorrect. I've updated the workbook with a dashboard that shows this. There's a screenshot below.
I assume I missed this in another post, but why are you not using the fiscal year built into Tableau?
You issue is that you need to partition by your fiscal year calculation, but cannot as it's in the second source. I think you will either need to join (rather than blend) the fiscal year data, use the in built FY concept, or look at some other way of getting all the dates you need.
Okay, a join fixes this - no need for table calcs. See attached.
I copied your data out to two separate sheets in Excel (but this would work for tables in SQL, text files in a directory, etc).
I then connected to this excel file, chose the data sheet, then multiple tables, add table (add the sheet with the FY data on it), click the Join tab at the top, chose Date = Date, left join on the dropdown (this means that if the fiscal date isn't found, you don't lose your data), add, okay.
Boom. We now have access to the FY info. I then copied your FY calculation across and the parameter, and sum([Sales]) works straight away (check the numbers to be sure).
PS. Sorry about the Boom.
Join.twbx.zip 457.8 KB
We cannot use Tableau's Fiscal Year feature because the fiscal year does not align to a month, it aligns to a 4-5-4 calendar. Under this calendar, the first day of 2012 was 1/27/2012. Tableau doesn't allow me to pick 1/27/2012 as the Fiscal Year start date, only Jan - Dec months. Most retailers use the AMC Calendar (a 53 week calendar that has a moving first day of the year - http://www.nrf.com/modules.php?name=Pages&sp_id=391). Unless I'm missing something, Tableau doesn't allow me to use the National Retail Federation's calendar unless I blend to it.
I'm trying to avoid a join for two reasons:
- The query is INCREDIBLY slow when I join to the Date Dimension, which is why I created the Fiscal Calendar extract.
- Business Analysis need to be able to leverage the Fiscal Calendar without dealing with the very large date dimension and having to create the parameter and calculated field each time they connect to data.
I really need a way to use the Fiscal Calendar data source with other data sources without having to put them all in the database. If you have any suggestions on how we can get there, I'd really appreciate it.
The solution Alex offered should be really fast. When you say the join query is incredibly slow, was that when using table calcs or just SUM([Sales])? The reason I ask is that as soon as you have table calcs and dates that are of the DATE or DATETIME data type, and are addressing along the dates, Tableau starts padding the domain (i.e. generating a row for every combination of dimensions and all of the dates, not just the dates that go with the dimension) and this can have a significant (horrific, really) impact on performance. A workaround for this is to create a string field for the date, use that in the view in place of the date, and set the compute using to that.
Also, in your original workbook the WINDOW_SUM(SUM([Sales])) could be modified to be faster, here's a common performance improvement:
IF FIRST()==0 THEN
The IF statement ensures there is only one row being returned (thereby preventing overlapping text), the IIF statement is a workaround that Richard Leeke put together for reducing the number of times Tableau is sorting the table data.
That said, these suggestions don't get around your issue #2. Given that you want to look at the Fiscal Calendar in a variety of aggregations, one suggestion would be to create your own Tableau data source that would include the parameter and the calculated fields, so users wouldn't have to rebuild that every time. With Tableau Server, you can even publish the data source there.
Agreed - of all of these, the join without table calcs should be the fastest - the FY info is not that detailed
Jonathan & Alex,
I've been offline for a week and apologize for not replying earlier to thank you for jumping in and offering some really good tips to resolve this issue. I realize I must not have described the issue clearly and oversimplified the sample workbook based on the most recent suggestions. I think I need to step back and explain the data sources we access and my sample workbook a little more fully.
My client uses a 4-5-4 calendar as their Fiscal Calendar. The first day of the year is in January, but somewhere in the last week and can change from year to year. Given this, I cannot use Tableau's Fiscal Year Start because it would miss the fact that most of February falls in the "January" fiscal month in this calendar.
The fiscal calendar (from their Date_Dimension) has many more fields than I supplied in the sample workbook. I trimmed down the complexity of the Fiscal Calendar that I provided to focus the discussion on the exact fields I was working with. In practice, I have created a Tableau Data Extract of the Date Dimension and setup a Fiscal Calendar Parameter and Calculated Field to do the aggregations. This extract is intended to be blended with other data sources using a Calendar Date. This seems to be where I'm getting into trouble.
Some notes about the data:
The data provided in the Need Help with Overlapping Text workbook was manufactured to illustrate the problem and not representative of the complexity of data that is being accessed. While joining the data sources from the workbook using Excel is simple and straightforward it would be prohibitive to take this approach with the production data.
Some of my client's data is accessible in aggregated tables where the needed fiscal date fields are provided. In this case, I can simply reuse the Fiscal Calendar Parameter and Calculated Field to do the aggregations and do not need to join to either the Date Dimension or the Fiscal Calendar Data Source/Data Extract.
Other data sources do not have the fiscal date dimensions and will not be updated to include them. It was in this second case that I was hoping we could use the Fiscal Calendar Data Source/Data Extract and blend using Calendar Date.
Not all of the data users are SQL Savvy and thus asking them to join to the Date Dimension in the database and create the Fiscal Calendar Parameter and Calculated Field to do the aggregations is difficult. Additionally, the join to the Date Dimension has proven very expensive and significantly slows dashboard performance based on a separate issue Tableau has with dates and the client's database implementation. This was another reason I wanted to use the Fiscal Calendar Data Source/Data Extract.
So, returning to the quandary. If I draw bar charts my dashboard display is fine using my Fiscal Calendar Parameter and Calculated Field. But some dashboards need to display crosstab data AND I wanted the functionality to allow users to aggregate that data to the Fiscal Calendar dimensions of Week, Month, etc., without having to access a separate report.
I tried Jonathan's suggestion using the formula
IF FIRST()==0 THEN
and found that I still had overlapping text. Maybe I'm missing something here or maybe it's because I'm still trying to use the Fiscal Calendar Parameter with this calculation.
I hope this better clarifies the situation and maybe the answer is I can't do this with Tableau, which would be really unfortunate. I look forward to your thoughts.