1 of 1 people found this helpful
You can create a date field if you combine the month and year fields. Have you got a package workbook?
Convert your Month and Year Fields to a string and use this calculated field if you don't have a day:
DATE("01" + " " + [Month] + " " + [Year])
Then right-click you new calculated field and select change data type to a date.
Thanks Stephen, this really helped! I need to calculate current month, and previous year in a table. I am getting stuck when it comes to Jan'16 to Dec'15. Any ideas!
4 of 4 people found this helpful
What I like to do is number my months based on the latest date in my data set. I use this calculation:
This will number your months going from 0, -1, -2, -3 etc. So for example from today:
March 2016 = 0
February 2016 = -1
January 2016 = -2
December 2015 = -3
and so on
This calculation dynamically changes as it's based on today's date so last month will always = -1. You can use a parameter if you like instead of TODAY() in the calculation to fix a specific date.
So say you want to look at last month Sales you would create a calculation that looks like this:
IF [Month Number] = -1 THEN [Sales] END
If you want to calculate your previous 6 months Sales you would use this:
IF [Month Number]>=-6 and [Month Number]<=-1 THEN [Sales] END
This numbering system is very handy as you can create a rolling year calculation:
IF [Month Number ]> -12 then "Current Year"
ELSEIF [Month Number] > -24 then "Previous Year"
Similarly you can use numbering to identify previous years using this calculation:
So for today's date (it being 2016) this returns:
2016 = 0
2015 = -1
2014 = -2
and so on
Therefore the previous year always = -1. So for example, if you wanted to know your sales for the previous year you would use:
IF [Year Number] = -1 THEN [Sales] END
Hope this helps!
This was extremely helpful! Thank you so much for explaining this.
What I am trying to achieve is -
I have 2 parameters one - Year and second - Month. So if I select Month = Jan and Year =2016 then in one row I want to see 2 columns -
Value for Jan 2016 , value for Dec 2015.
Now since I am using a Year parameter (as it is single select), I am unable to get value for Dec 2015 and if I put a condition ( Year <= Year Param) then I get all the values from the beginning. I want to get just one month prior value.
I have tried all the tricks available online but I still can't get the desired o/p. Can you please help me with this.
Also, I do not want to use Today () in any of the calculations.
Can you upload a package workbook? It will be easier for me, and others, to see how to help.
Here is the tableau file - I want to display only current month and previous 1 month value for a selected year and month.
Currently, I have one sheet for previous month and another one for current month. The current logic works for all months except for the criteria where Current Month/Year = Jan'16 and Previous Month/Year = Dec'15.
Let me know what can be done to solve this!
Thanks a lot.
Test1.twbx 22.4 KB
Stephen the file doesn't open on my laptop. I have tableau 9.1 on my laptop. Can you send it again, please! Thanks a lot!!!
I am guessing your version is different that mine. If you cannot save as 9.1 then can you paste the logic here, whatever works for you! Thanks a lot once again!
Can someone please help me out with this! I have exhausted all my options! Thanks in advance!
You're hurting yourself with all the string variables.
I made INT variables for year and month.
And I made INT parameters for year and month.
You don't have enough data to show both previous month AND previous year at the same time, but the logic in the calcs will work when enough data is in there.
I wrapped the previous year and previous month calcs in the ZN() function so that at least you get zero when no data is there.
In the previous month calc, you have to subtract 1 from the year if you go backward from January to December. You'll see logic in that calc for that. I could have incorporated the [Month I want] calc into the [get previous month] calc the same way I subtracted a year, but I broke it apart to show the methodical progression of steps you need to do there.
Thanks Joe for your help! This is helpful but doesn't help me achieve what I am looking for. I am looking to display RM_ID, Month_Desc, Previous Value and current Value. I want to use Year and month parameters to filter and show just the selected values.
If we select year param = 2016 and Month param = Jan then display all RM_IDs which have Jan'16 value and Dec'15 value. I am using these 2 parameters to show current data in my other sheets as well so want to be consistent all across. Thanks once again for all your help!!!