Sorry, I just realized I never responded to your previous post. I think what should be used is the window_max table function. So the calculation might look similar to the following:
if max[Report Date]=window_max(max([Report Date])) then sum([Premium]) end
Then, the way the calculation is being computed will need to be edited (to do this, right click on it and select Edit Table Calculation...)
Hope this helps! If it doesn't, perhaps you can post a sample workbook (twbx file)?
sorry for the novice questions, but I'm a new user to Tableau. If I save the workbook and attach it, will you be able to use the workbook with the data source I created, or do i have to import the data? My data source is currently linked to a SQL server database using the "connect live" option.
Attached is the sample workbook of my table.
I kept it simple and have 1 policy per month and all the Inforce Premium amounts are $100.
So there are 10 rows for 2011 and 8 rows for 2012 (up to 8-31-2012).
You can see from the simple graph that it's aggregating the yearly total of the Inforce Premium by adding all of the months together for the year. So you get $1200 for 2011 ($100 X 12 months) and $800 for 2012 ($100 X 8 months).
What i want is the amount (Sum of Inforce Premium) for any records with a Date of 12-31-2011 ($100 for 2011), 12-31-2012 ($100 for 2012), etc..
Also, I wanted to be able to show Quarterly amounts, for example the Sum of Inforce Premium for any records with Report Date of 3-31-2012 for Q1, 6-30-2012 for Q2, and so on.
I also wanted to be able to get monthly sums of Inforce Premium as well.
I think the issue is the fact the the rows are added only at the end of each month with the last day of month as the Report Date. That is what is throwing me off on how to make the calculations.
thanks for you help.
test.twbx.zip 17.7 KB
How about the attached? Use the parameter to set the resolution.
Notice that I turned Report Date into a string, this is to work around Tableau's domain padding when using a date pill for compute using.
I also added another worksheet so you can see what is happening.
Let me know if you have any questions.
Tracy, this is another situation with LAST() combined with compute using makes more sense than WINDOW_MAX()
test jm edit.twbx.zip 44.5 KB
That's helps. However, my Report_Date (copy) is not sorting within each year. I would expect it to go from Jan thru Dec for each Timeframe, however, it is not doing that. I tried different sorting mechanisms, but it's not working.
Is there something i am missing?
one more thing. I also noticed that in my worksheet, my dates are in the format:
Dec 31 2008 12:00AM
Your date is in the format:
You can apply a sort to the Report_Date (copy) pill, sorting Ascending on Minimum Report_Date.
Your data source determines what the text format of STR([Date]), and I was taking advantaged of the Tableau Data Engine format for default sorting.
I just added the REPORT_DATE (copy) field to my extract from SQL server. Now it comes across in the correct format.
It looks ok in the Yearly view, but when I change it to Quarter or Month, it is no longer sorted correctly. Is there a way to make the whole result set be sorted by the Report_Date (copy)?
not sure why it is not working on my end. I'm using another data set, same formating, but with "real" data now. It should work the same as the test package I loaded.
Here is how my dashboard looks after the sort. It's sorting by Timeframe insead of Report_Date_Str like it does on the Yearly view:
The Timeframe pill has no sorting on it, apply the same sorting to that pill as well.
Got it now. I'll get better with this as time go on...
One other quick question. I want to show a line graph of the Inforce over time, but because the Report_Date was changed to Text, it won't let me create the line graph. It stating it needs a "Date" field. If I use the original Report_Date field, then the other calcs dont' work. Any ideas?