
1. Re: Multiple Measures Across PeriodtoDate Calculations
Alex Kerin Apr 28, 2011 5:34 AM (in response to Guillermo Cabiro)Updates daily in that it uses today's date? Yes is the answer using today() in calculations  see http://www.datadrivenconsulting.com/2011/01/tableauasadashboardtaketwo/ as a guide to the type of calculations you could do  this one isn't based on today(), but you should see where you could use it...

2. Re: Multiple Measures Across PeriodtoDate Calculations
Guillermo Cabiro Apr 28, 2011 12:32 PM (in response to Guillermo Cabiro)Alex, Thanks for the comment. To obtain the this crosstab is very easy in other BI solutions, including some applications that are 20 years old. There has to be a good & easy way to accomplish this with Tableau. Any ideas?

3. Re: Multiple Measures Across PeriodtoDate Calculations
Joe Mako Apr 28, 2011 1:42 PM (in response to Guillermo Cabiro)Bill,
Here is an example workbook of what I think you may be looking for.
I created a few worksheets without reshaping your data, and the calculation for one measure of interest. I then reshaped the data, as described at http://www.tableausoftware.com/support/knowledgebase/preparingexcelfilesanalysis and created worksheets based on this reshaped data for all measures of interest.
Please let me know if this is what you are looking for, or if there is anything else you would like to see.

crosstab_ytd.twbx 500.5 KB


4. Re: Multiple Measures Across PeriodtoDate Calculations
Joe Mako Apr 28, 2011 10:22 PM (in response to Guillermo Cabiro)Here is another approach, using your original data structure, a few quick table calculations, and a parameter, you can see lots of interesting things.

ytd_table_calculations.twbx 90.0 KB


5. Re: Multiple Measures Across PeriodtoDate Calculations
Guillermo Cabiro May 1, 2011 9:50 PM (in response to Guillermo Cabiro)Joe, Thanks for your valuable help. It works now. Do you have any tips on how to calculate Measures for the Last 52 Weeks and Previous 52 Weeks?
In order to see change and growth %.
Regards, Bill

6. Re: Multiple Measures Across PeriodtoDate Calculations
Joe Mako May 1, 2011 9:53 PM (in response to Guillermo Cabiro)Instead of the This/Last Year YTD calculations, you can use these:
Last 52 Weeks
SUM( IF DATEDIFF('week',[Cal Year/Mo/Day],TODAY())<=52 AND DATEDIFF('week',[Cal Year/Mo/Day],TODAY())>0 THEN [Cost of Sales] END)
and
Previous 52 Weeks
SUM( IF DATEDIFF('week',[Cal Year/Mo/Day],TODAY())<=104 AND DATEDIFF('week',[Cal Year/Mo/Day],TODAY())>52 THEN [Cost of Sales] END)

7. Re: Multiple Measures Across PeriodtoDate Calculations
Juracy Americo Jul 22, 2011 1:07 PM (in response to Guillermo Cabiro)Hi everybody, I was studying your formulas, very elegant solution, but I use thirteen periods and each with Four week in a year rather than the normal calendar with month (P01, P02 ....P13) and I have a table with the date (normal calendar) and each period in a separated column, so how can I use same approach to let the users choose not only "day, month and year" but "day, period and year"

8. Re: Multiple Measures Across PeriodtoDate Calculations
Joe Mako Jul 22, 2011 1:24 PM (in response to Guillermo Cabiro)There are a few way to accomplish what I think you are looking for. To be sure, can you please provide a sample packaged workbook that represents your situation, and describe what you expect for a result, and what interaction you want to allow for? With that, an example can be made for you.

9. Re: Multiple Measures Across PeriodtoDate Calculations
Juracy Americo Jul 23, 2011 8:32 AM (in response to Guillermo Cabiro)Hi Joe thank you for your answer
Sure I can provide a sample packaged workbook with my situation and I have it in attachment :
He is the situation, What I would like to accomplish is to let the user choose not only between day, week, month and year by I need to include another option which is period, I couldn't make it work as well for week , there is something I'm missing but I could't figure it out.
In the attachment I have a workbook with 6 fields , one of then with date and another with period as you can see.
REGION
PERIOD
DATE
KPI  1
KPI  2
KPI  3
So any tips or help will be appreciated
Thank you in advanced for your time

sample_data.twbx 42.9 KB


10. Re: Multiple Measures Across PeriodtoDate Calculations
Joe Mako Jul 23, 2011 6:11 PM (in response to Guillermo Cabiro)As for adding date trunc option for your Period, how about this calculated field:
DATE(CASE YEAR([DATE]) WHEN 2010 THEN #1/4/2010# WHEN 2011 THEN #1/3/2011# END + (FLOAT(RIGHT([PERIOD],2))1)*28)
As for the parameter selection week not working, what do you want for a result? Currently in your formula for "Level Filter", you do not have a condition for week, so no data is displayed. If you add another line like
WHEN "WEEK" THEN IF YEAR([DATE])=YEAR(TODAY())1 THEN 1 ELSE 0 END
then all the values in in the year previous from Today's year will be displayed. Additional logic can be added depending on exactly what you want.
I am not quite sure what else you are looking for. If you can be specific in what you want calculated or compared, more details can be provided.

11. Re: Multiple Measures Across PeriodtoDate Calculations
Juracy Americo Jul 25, 2011 2:04 PM (in response to Guillermo Cabiro)Hi Joe thank you for your reply.
Sorry Joe for not be more specific.
You give a solution with the calculated field for Period
But I'm not quite sure if I understanding your formula, do you might to explain it? Specially the line with WHEN 2010 THEN #1/4/2010# or WHEN 2011 THEN #1/3/2011#.
I realize that I don't need the Level Filter calculated field
To give a better idea what I try to do is a dynamic visualization updated with This year YTD and Last Year .
To help with it a sample image to show what I need to accomplish I don't know if is possible in tableau
Thank you

last_year_vs_actual__year.jpg 47.1 KB


12. Re: Multiple Measures Across PeriodtoDate Calculations
Joe Mako Jul 25, 2011 4:43 PM (in response to Guillermo Cabiro)1/4/2010 is the first date for P01 2010, so if you wanted a truncated date for all of P01 2010, 1/4/2010 would be the date. 1/3/2011 is the first date for 2011.
Upon thinking about this some more, that may not be the best way to approach this, that was just my first pass at it.
As for a remake of you image, how about the attached?

sample_data_2_edit.twbx 50.8 KB


13. Re: Multiple Measures Across PeriodtoDate Calculations
Juracy Americo Jul 26, 2011 5:02 AM (in response to Guillermo Cabiro)Thank you for your explanation, I got it.
Very interesting your visualization, I like it so much, the only think I miss is the parameter controller to let the user choose between day, week, period and year
I guess if I wanna have normal calendar e period calendar in same visualization is difficult , maybe I should keep two dashboard one for normal calendar ( day, week and year ) and a second one for period calendar ( period ) .
Any suggestion will be appreciated.
Joe thank you for you help and for share you knowledge with us

14. Re: Multiple Measures Across PeriodtoDate Calculations
Joe Mako Jul 28, 2011 8:36 PM (in response to Guillermo Cabiro)One option when you want to switch between sufficiently different charts is to use a layout container on a dashboard, and a calculated field to filter on like in the attached.

sample_data_edit_3.twbx 65.8 KB
