12 Replies Latest reply on Jan 23, 2018 7:25 PM by Shinichiro Murakami

# Month and Quarter Calculations?

Hi All,

I'm stuck up at month and quarter calculations. I have data where i have information about the date and value fields. The data gets loaded once every month.

For example:

Date                        Value

2017-1-31               10000

2017-2-28               10100

2017-3-31               10200

2017-4-30               10300

2017-5-31               10400

2017-6-30               10500

The value here acts as the ending value. So here the Jan month value becomes the beginning value of Feb month and the value 10100 for the Feb month from the database is the ending value. So here I need a month calculation, where if a user selects the Feb 2017 month they should see 10000 as the beginning value (which is the ending value of Jan 2017)  and 10100 as the ending value. Similarly, when they select the Feb 2017 month they should see 10100 as the beginning value (which is the ending value of Feb 2017)  and 10200 as the ending value.

For Quarter Calculation, lets say if it is 1st quarter of 2017, then we should see the beginning balance as of Jan 2017 and ending balance as of March 2017

And if it is 2nd quarter of 2017, then we should see the beginning balance as of Apr 2017 and ending balance as of June 2017.

Also attached the sample excel file for reference.

Thank you.

• ###### 1. Re: Month and Quarter Calculations?

Hi, Samanth

Hope this helps.

Regards,

Shin

• ###### 2. Re: Month and Quarter Calculations?

Hi Shinichiro,

Thanks for the response. Yes it does help. But, lets say if we have both the Beginning and Ending Balance for a Date. How should we write the Quarter Calculation and is it possible to provide the user with a single filter or parameter to show both the Months and Quarters of an year/s so that they can pick month or quarter as per there requirement

Example:

2016 Jan

2016 Feb

2016 March

2016 Q1

2016 Apr

2016 May

2016 June

2016 Q2

.......

Please find the attached image with Beginning and Ending Balance for a Date.

Thank you.

• ###### 3. Re: Month and Quarter Calculations?

Hi Samanth

Your requesting feature is not very clear to me.

Which user's behavior should bring what?

And what should be shown on table?

Could you illustrate with hand drawing or something more clearly?

Thanks,

Shin

• ###### 4. Re: Month and Quarter Calculations?

Hi Shinichiro,

My first question, if we get both the Beginning and Ending Balance for a Date field from the query. How should we write the Calculation to get the Beginning and Ending Balance of a Quarter in Tableau.

For example:

Date                   Ending Value     Beginning Value

2017-1-31               10000                    0

2017-2-28               10100                  10000

2017-3-31               10200                  10100

2017-4-30               10300                  10200

2017-5-31               10400                  10300

2017-6-30               10500                   10400

Second Question, As we are showing both month and quarter beginning and ending Balance on a dashboard.

Is it possible to provide a dropdown Date filter which can give an option to select by either month or quarter of a year/s

Please see the attached Images for month/year and quarters filter.

Can we provide a single date filter which has options to select 2017 Jan if they want to see Jan month data or 2017 Q1 if they want to see Quarter 1 data of 2017.  Is there any work around in Tableau?

Thank you.

• ###### 5. Re: Month and Quarter Calculations?

Hi Samanth

See attached.

Thanks,

Shin

• ###### 6. Re: Month and Quarter Calculations?

Hi Shinichiro,

You the Man. Thanks a lot.

Need one last help. I'm attaching my sample workbook. Can you please do the calculations in the attached workbook and reattach the file back so that i can understand without doing union of sheets as I'm getting the data from database.

Thank you.

Regards,

Samanth

• ###### 7. Re: Month and Quarter Calculations?

Hi Samanth,

See attached.

Not from inbox view but from original post.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 8. Re: Month and Quarter Calculations?

Thanks a lot Shinichiro .

• ###### 9. Re: Month and Quarter Calculations?

Hi Shinichiro,

Need Small help. Please see the attached images.

Question 1) Is it possible when we select the month/quarter filter (ex: 201711 or 2017Q1) to show the entire portfolio balance(i.e; the Ending FV) for the 2017 year for all the months?

Right now I'm using a separate filter for the Portfolio Balance. Is it possible to use only month/quarter filter which works for both the sheets.

Question 2) For the segment premium wine, if I select the 2016 Q3 or 2017 Q1 the End Balance shows as Null. Is there any way we can change the calculation for the Quarter, let's say for 2016 Q3 the End balance should be as of September. But as we don't have any premium wine Value for September can we show the End balance as of August for 2016 Q3 as we have the end balance for August 2016. Similarly, for 2017 Q1 the End balance should be as of March. But as we don't have any premium wine Value for February and March can we show the End balance as of January for 2017 Q1 as we have the end balance for January 2017.

Attached the workbook.

Thank you.

• ###### 10. Re: Month and Quarter Calculations?

Hi Samanth

It is getting confusing me and request is already very different from original.

Thanks,

Shin

• ###### 11. Re: Month and Quarter Calculations?

Hi Shinichiro,

Here is the link to the new post thread. It would be great if you can help me out on this.

Thank you.

Quarter Calculation and using a single filter for two different sheets?