12 Replies Latest reply on Oct 24, 2017 9:01 AM by Iestyn Humphreys

# First (beginning of period) and last (end of period) in any given period in a table

I am trying to report in Gold.year.T.001 worksheet the value of the Gold LBMA measure at the beginning of each year that is 1 January and at the end of each year that is 31 December or the latest value in that specific year.

Note that Date "dimension" is every day from 1 January 1900 to current day in 1 day increments.

I am sure there is a calculation for this and is there one which generally works for the beginning and end of quarters or months on the same basis?

Can anyone assist with this?

Regards

• ###### 1. Re: First (beginning of period) and last (end of period) in any given period in a table

Hi Iestyn,

everything would be easier if you could share a workbook!

Anyway, try creating:

• MAX DATE as {FIXED YEAR([Date]):MAX([Date])}
• MIN DATE as {FIXED YEAR([Date]):MIN([Date])}
• NEW MEASURE as if [Date] =  [Max Date] or [Date] =  [min Date] then [Gold LBMA pm (US\$/oz)] ELSE 0 END

Let me know if it helps!

• ###### 2. Re: First (beginning of period) and last (end of period) in any given period in a table

Many thanks,

I uploaded a twbx as opposed to a twb as the message informed me it was easier for those to work with a twbx?  is this not the case?

"You attached a .twb file instead of a .twbx file (Tableau Packaged workbook).  A .twbx file allows other users to open the file with the data so that they can better help you.  For more information on how to create a packaged workbook, go here."

In any event please find the twb as well.

I will look at the note below and try and incorporate.  If I have any further questions will follow up later today.

Regards

• ###### 3. Re: First (beginning of period) and last (end of period) in any given period in a table

There's no data within it, please share the sample data too!

P.S.

It's a good practice to share twbx, just open again you workbook and save as.....twbx

• ###### 4. Re: First (beginning of period) and last (end of period) in any given period in a table

Here's a screenshot about the results!

• ###### 5. Re: First (beginning of period) and last (end of period) in any given period in a table

Francesca,

I have attached both twb, twbx as well as the original excel workbook.

Is this what you required?

If you could insert the calculation and send this back that would be great.

Regards

• ###### 6. Re: First (beginning of period) and last (end of period) in any given period in a table

Here you are!

Let me know if it helps you!

If not feel free to ask for further info!

• ###### 7. Re: First (beginning of period) and last (end of period) in any given period in a table

Thank you very much that’s great.

My only follow up question is as follows.

Is there a quick way to change the Calculated Min and Max so that they work for when the Year (Date) is expanded to Quarter (Date) and Month (Date) that the values are also recorded as at the moment these are all zeros apart form the beginning of the year and the end of the year.

Regards

First (beginning of period) and last (end of period) in any given period in a table

• ###### 8. Re: First (beginning of period) and last (end of period) in any given period in a table

Many thanks that was a great help.

To follow up my only question is how can we get the calculation to work when we expand to include Quarter (Date) and Monthe (Date)?

• ###### 9. Re: First (beginning of period) and last (end of period) in any given period in a table

Beginning of Quarter as {fixed [Quarters]:  MIN([Date])}

Ending of Quarter as {fixed [Quarters]:  Max([Date])}

Gold_BOQ as if [Date] = [Beginning of Quarter] then [Gold LBMA pm (US\$/oz)] else 0 end

Gold_EOQ as if [Date] = [Ending of Quarter] then [Gold LBMA pm (US\$/oz)] else 0 end

Does this help?

• ###### 10. Re: First (beginning of period) and last (end of period) in any given period in a table

Many thanks for this managed to resolve and works if I have separate tables to display, years, quarters and months.

Is there no way to have this in one worksheet which dynamically updates when i drilldown from years to quarters to months with the calculated field returning the beginning of period and end of period value depending on what level is drilled down to?

Regards

• ###### 11. Re: First (beginning of period) and last (end of period) in any given period in a table

You can just use the Date field.

BOY as {fixed year([Date]):  MIN([Date])}

EOY as {fixed year([Date]):  Max([Date])}

BOM  as {fixed DATETRUNC('month',[Date]): MIN([Date])}

EOM as  {fixed DATETRUNC('month',[Date]):  Max([Date])}

G_BOM as if [Date] = [BOM] then [Gold LBMA pm (US\$/oz)] else 0 end

G_EOM as if [Date] = [EOM] then [Gold LBMA pm (US\$/oz)] else 0 end

G_BOQ as if [Date] = [BOQ] then [Gold LBMA pm (US\$/oz)] else 0 end

G_EOQ as if [Date] = [EOQ] then [Gold LBMA pm (US\$/oz)] else 0 end

Thanks,

Pratima.

• ###### 12. Re: First (beginning of period) and last (end of period) in any given period in a table

Thank you very much that’s very helpful.

Regards

Iestyn Humphreys FIMMM, AIME, PhD

Chairman & Corporate Consultant (Due Diligence)

SRK Consulting (UK) Limited

5th Floor, Churchill House, 17 Churchill Way, Cardiff, CF10 2HH, Wales, UK

Tel:  +44 (0) 2920 348 150; Fax: +44 (0) 2920 348 199

Mobile:+44 (0) 78 4180 0092; Direct: +44 (0) 2920 347 889

Email: ihumphreys@srk.co.uk<mailto:ihumphreys@srk.co.uk>; Skype: Iestyn.humphreys.srk

www.srk.co.uk<http://www.srk.co.uk/>

SRK Consulting (UK) Limited (“SRK”) is registered in England and Wales with Registered Number 01575403 and Registered Office at 21 Gold Tops, Newport, NP20 4PG, Wales, United Kingdom.

CONFIDENTIALITY AND DISCLAIMER STATEMENTS

This email transmission (including any attachments to it) is strictly confidential and may be legally privileged. This email is intended solely to be received by the person(s) or organisation(s) to whom it is addressed. If you are not the intended recipient of this email, retaining, using, disseminating, distributing, copying or taking any actions in reliance upon any information or documents contained in it is strictly prohibited and will constitute a breach of confidentiality.  If you have received this email in error, please reply to the sender immediately to inform us and delete it.

Any statements, views or opinions contained in this email are personal to the sender except where the sender expressly states these are attributable to SRK or any company in SRK's group.

Please note that internet email is not a 100% secure communications medium. Further, any material originated by SRK and transmitted to you via a third party should be verified by reference to material obtained directly from SRK.  Although we have taken steps to ensure that this e-mail and attachments are virus-free at the point of departure, the recipient should check that they are virus-free upon receipt and we do not accept liability for any damage so caused.