1 2 Previous Next 22 Replies Latest reply on Mar 28, 2018 5:52 AM by Anthony Griffiths

# Help Needed: Same Month Previous Year Calculation

Hi everyone

I'm a bit stuck, I can't see what I'm doing wrong so any help will be appreciated.

I have a chart which is displaying the Average Cost Per Job (Cost/No. of Jobs) which is split by Area.

This works as intended.

What I'm trying to add on is a dual axis shape to show the Average Cost Per Job for the same month in the previous year based off whichever month the user selects from the [Date] field (Single Select Only)

So to do this i'm using the following calculated field:

Average Cost Per Job Previous:

{ FIXED [Area]:

SUM(

IF

//[Date] = #01/04/2016#

THEN

[Cost]

END)

/

SUM(IF

//[Date] = #01/04/2016#

THEN

[No. of Jobs]

END)

}

When I do this I get nulls (5 Nulls seen in image above), but, as you can see from the commented out lines, if I manually type in the date for April 2016 for example, it works exactly as intended.

I've also tried the MAKEDATE function to work out the previous year but nothing seems to be working.

I'm connected to an excel spreadsheet via an extract. Using Tableau v10.2

• ###### 1. Re: Help Needed: Same Month Previous Year Calculation

Anthony,

• ###### 2. Re: Help Needed: Same Month Previous Year Calculation

Sheet 4 is what I'm currently working on

• ###### 3. Re: Help Needed: Same Month Previous Year Calculation

hi anthony,

you want this,

• ###### 5. Re: Help Needed: Same Month Previous Year Calculation

hi

thanks for the screenshots, but the numbers don't look right

In the first image, the yellow 'X' should be showing March 2016 data, but there isn't any, so not sure what it's looking at

as these are screenshots, how did you calculate it?

• ###### 6. Re: Help Needed: Same Month Previous Year Calculation

hi

• ###### 7. Re: Help Needed: Same Month Previous Year Calculation

Hi

this is using today's date, which i'm not using

I want the user to specify the date, based on the [Date] filter

so if they selected April 2017;

The blue bar would should April 2017's value and the Yellow 'X' would show April 2016 value

• ###### 8. Re: Help Needed: Same Month Previous Year Calculation

Please find the attachment for Solution.

Create a parameter first and use Add from field [Date] Dimension.

Replace [Date] by Parameter in your calculated field

Change [Date] filter by new calculated field [Date Filter]

• ###### 9. Re: Help Needed: Same Month Previous Year Calculation

Hi Anthony,

see below screenshot this is helpful for you if you want to specify the date you can use parameter

Thanks,

Veeru.

• ###### 10. Re: Help Needed: Same Month Previous Year Calculation

Thank you for this, can give it a try

I've tried to avoid using parameters as they are not dynamic so when the data source is reloaded, the parameter doesn't update automatically unless I manually go in and update it

• ###### 11. Re: Help Needed: Same Month Previous Year Calculation

Hi Anthony,

Thanks for your reply, it is ok for you to mark as helped and correct

Thanks,

Veeru.

• ###### 12. Re: Help Needed: Same Month Previous Year Calculation

I would but don't believe it's what I'm after

As I said above, the parameter works but, correct me if I'm wrong, parameters are static

so if i had this dashboard on the Tableau Server reloading every day, the parameter won't change as it's a static list so doesn't really fit my requirements

• ###### 13. Re: Help Needed: Same Month Previous Year Calculation

hi,

you can take a parameter below screenshot parameter automatically update

and use the filter below screenshot

Thanks,

Veeru.

• ###### 14. Re: Help Needed: Same Month Previous Year Calculation

Hi,

How do you get to that menu option? I don't see it

I'm currently using 10.2, is this a version issue?

1 2 Previous Next