see the attached
not certain I fully understand but it appears that you were not applying the parameters to your filters correctly
the current date needs to be based on today()
Not certain what this is suppose to do but it looks like if should be based on current month
then you need to apply a top 10 filter as shown
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
Top 10 Gross Adds_v10.5.twbx 17.7 MB
This is because your month year filter is already filtering the dataset.
So all your current month field is doing is changing the name of the month year. It's not filtering the data in any way.
So your current month may say june, but actually it is still referring to the July month. Your calculation is just doing minusing a month from the actual month.
If you want your filters and parameters to work, you need to remove the My(Month) and put all your conditions in calculated fields.
Please use the following steps:
1) Put your normal month year discrete field in rows
2) Create the below calculation
3) Bring this calculated field to your filters shelf
Finally to get the top 10 master agent, update your filter to look at top 10 based on gross add subs sum:
Thanks and please see the attached workbook.
I think it was saved in 2018.1 mine is 10.5 can you please convert it and attach. Thank you once again.
Wow that was a genius work.It worked, Thank you so much .
Could you please explain how this formula is working. I am not able to understand. Would be helpful in my future works (For the readers too). Thank you.
Thanks for your Response and time Jin. I have got the answer from Mavis. I hope the workbook provided by her will help you to understand my requirement.
1 of 1 people found this helpful
Sure no problem, so when All is selected for the Show parameter, the logic is as follows:
Note: Date truncate truncates the date down to the date part specified. E.g. 05/06/2018 using a date trunc for month becomes 01/06/2018.
The month-year of the date has an extra X number of months added to it based on your Last Months Parameter. When this is greater than today's month-year, then it will be set as true.
So for example, if say we select 3 for Last Months and today's month is June 2018, then we are looking for April, May and June 2018.
If we add 3 months to April, this becomes 'July', May becomes 'August' and June becomes 'September'. All these months are greater than June 2018 so are valid. However, then it becomes a bit endless because July can then become October, August becomes November... and these are greater than June.
As you happen to have data which is greater than today's date, then we need to tell tableau that we do not want to go further than today's date. Therefore we have the next bit:
Which tells Tableau that the months must be BEFORE or EQUAL to the current month.
With the next part of the calculation where we only look at completed months, it is very similar apart from the fact that we are shifting it by one month:
So this time we are adding an extra month to the Last Months Parameter. So in this example, we are actually looking at the previous 4 months minus the month we are currently in:
The last bit of the calculation says that the months must be LESS than today's month. ie June is no longer included. So the completed parameter only looks at the March, April, May and excludes June.
Does that help?