7 Replies Latest reply on Feb 24, 2017 2:24 PM by Maria Trumble

# YTD Calculations

HI

I am trying to create a ytd calculations based on some data.

However my month dimension is not date formatted, but is string as it is just Jan, Feb, mar etc

I currently have two sheets

The first sheet is month of income statement, works a treat

Second sheet is ytd income statement

I have attached a screen shot of my income statement, any help you are able to give would be much appreciated

It would be great if the ytd sheet is driven by the month of filter on my first sheet

Example

Income Statement Month of = Feb

Income Statement YTD = Sum of Jan and Feb

• ###### 1. Re: YTD Calculations

you can change your "MONTH" dimension to date data type and add it to the work sheet

Then change it from year to Month and try your calculations,

• ###### 2. Re: YTD Calculations

Thank you for your quick response.

The Month is hard keyed at source, if I change in alteryx or tableau to

date format it turns the field to null.

I think the answer is I am going to have to put it in as the first day of

the month, I was hoping there might be a work around instead of me

changing the original process

Thank you

Maria Trumble

Tel: 01733 582331

Tie Line: 7-529-2331

• ###### 3. Re: YTD Calculations

Hi Maria,

Find my approach as reference below and stored in attached workbook version 9.3 located in the original threat

Step 1: Define "Month number

if [Month]="Jan" then 1

ELSEIF [Month]="feb" then 2

ELSEIF [Month]="mar" then 3

ELSEIF [Month]="apr" then 4

ELSEIF [Month]="may" then 5

ELSEIF [Month]="jun" then 6

ELSEIF [Month]="jul" then 7

ELSEIF [Month]="aug" then 8

ELSEIF [Month]="sep" then 9

ELSEIF [Month]="oct" then 10

ELSEIF [Month]="nov" then 11

ELSEIF [Month]="dec" then 12 END

Step 2: Define Date

date("1-"+str([Month number])+"-2017")

Step 3: Define YTD

if DATEDIFF("year",[Date],today())=0 and DATEDIFF("month",[Date],today())>=0 then [Value] END

• ###### 4. Re: YTD Calculations

This is a very interesting approach.

Please do also see the links below on date parsing function available in tableau. This also could help

• ###### 5. Re: YTD Calculations

Thank you so much for this.  I used the Dateparse and it worked.

However I am still struggling with creating a YTD value.

• ###### 6. Re: YTD Calculations

Hi Maria,

Regards,

Norbert

• ###### 7. Re: YTD Calculations

Unfortunately not, I have attached a screen shot.

This is my summarised income statement.

In a perfect world it would be great if when the month is changed the values would then sum to be come ytd values.  at the moment they are just month of values