9 Replies Latest reply on Jun 21, 2018 7:16 AM by Mavis Liu

# Calculation Help.

Hi Everyone ,

I have created a dashboard which shows the data for last 3,6,12 and 24 months based on the period selected. User has asked me to add a toggle which has two values

'Show all Months' and 'Show only completed Months' .  For this I have created a parameter and have tried get to fetch only last months . Date names are changing but the values remains the same at the cell. I mean if i select all months values for July 2017 will be 1863 . Now if i select "Completed Months" June will come in place of "July" but the value 1863 remains at the June. I am very confused and I have been trying to achieve this since Yesterday. Please help me in this regard. Sample workbook is attached.

Let me know if my explanation was not clear , I will rephrase my sentences. Thank you.

Regards,

Deepak

• ###### 1. Re: Calculation Help.

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

Jim

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.

• ###### 2. Re: Calculation Help.

HI Deepak,

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.

Thanks,

Mavis

• ###### 3. Re: Calculation Help.

Hi Deepak,

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.

Mavis

• ###### 4. Re: Calculation Help.

Hi Mavis ,

Thanks for your response. I am not able to open your workbook,getting below error. Can you please re attach the file. Thank you.

• ###### 5. Re: Calculation Help.

I think it was saved in 2018.1 mine is 10.5 can you please convert it and attach. Thank you once again.

• ###### 6. Re: Calculation Help.

Hi Deepak,

Please try below.

Thanks,

Mavis

1 of 1 people found this helpful
• ###### 7. Re: Calculation Help.

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.

• ###### 8. Re: Calculation Help.

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.

• ###### 9. Re: Calculation Help.

Hi Deepak,

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?

Thanks,

Mavis

1 of 1 people found this helpful