I have gone through the explanation that you provided in your post. However I am still facing difficulty in understanding the calculations used. Would you be able to share the excel with Formulas so that we can look into that and try to share some information on this.
Tableau works differently with data. Date format should be with year.
So you have to reshape the data in excel in the below format.
Date Value Cummulative Projected 4/1/2015 12 252 4/1/2016 20 306 306 4/2/2015 16 268 4/2/2016 17 323 323 4/3/2015 11 279 4/3/2016 31 354 354 4/4/2015 23 302 4/4/2016 39 393 393 4/5/2015 20 322 4/5/2016 28 421 421 4/6/2015 33 355 4/6/2016 36 457 457 4/7/2015 34 389 4/7/2016 5 462 462 4/8/2015 19 408 4/8/2016 0 507 544 4/9/2015 29 437 4/9/2016 0 536 749 4/10/2015 18 455 4/10/2016 0 554 831 4/11/2015 57 512 4/11/2016 0 611 913 4/12/2015 55 567 4/12/2016 0 666 995 4/13/2015 57 624 4/13/2016 0 723 1077 4/14/2015 56 680 4/14/2016 0 779 1159 4/15/2015 50 730 4/15/2016 0 829 1241
Set Date to Datetime datatype
Add MONTH(Date) and DAY(Date) to Columns
Use dual axis and synchronize for Cumulative and Projected pills to Rows.
Check this for more info on how to do it -- Dual Axes
You can also use the below calculation for month/day display:
STR(DATEPART('month', [Date])) + '/' + STR(DATEPART('day', [Date]))
Add this calculation to column
Workbook Attached, v9.0
Hi Nirupam, thank your for the valuable information. One of our goals is to stream line the work involved. The data that I have in Tableau currently is a direct pull from a table in our DB. It sounds like from your suggested solution that I should modify the information from within Excel, I should have stated that we are trying to remove Excel for the equation. I have attached a spreadsheet that depicts the data as pull from the table directly. Would it be possible to provide a solution using this data, or would I have to create a table in the DB which has new derived values/columns?
Thank you, Chris
SIR Ever.xlsx 123.6 KB
Date format provided in the new excel works in Tableau.
However I do not see cumulative and projected values in the new excel file. I'm not sure what logic you are using in order to derive them.
If you can derive them by modifying the current sql code then good, or else you need transform the data which has cumulative and projected values and load it into a new table in DB.
Hi Nirupam, the second Excel sheet I provided (SIR Ever.xlsx) is a direct pull from a database table. It sounds like from your previous discussion Tableau is unable to handle generating cumulative numbers and projected values like my scenario? I will look into creating derived values for them in the DB. The cumulative values should be straightforward, although the projected may be more challenging. In the meantime, I will continue using Excel.
Thank you for the helpful information.
I'm glad it helped , please mark the answer as correct so the thread can be closed.