5 Replies Latest reply on Jun 7, 2016 5:45 PM by swaroop.gantela

# Complex Date calculation_Custom Date levels

Hello Folks,

I need a help from this community regarding a date calculation we are trying to achieve. I have created a replica using the superstore data. The requirement is I would like to view a graph of sales Vs month for different segments.

Now I know tableau would allow me to show the date is continuous or discrete format, however we need to show it as Month 1, Month 2, Month 3 etc. Month 1 - should be from any start date of a month till the end date of the same month. Then comes Month 2 and then so on.

We have a dataset where we have various dates of different years. In order to avoid the visual problem we need to create a new calc.

However even if we create a calc field can someone please tell me how to use it as a date level? like we have the order date?

The graph should be the month 1, 2, 3..... vs the sum(Sales)etc (instead of the discrete or continuous months).

• ###### 1. Re: Complex Date calculation_Custom Date levels

Somok,

Maybe you can try using a calculation like:

ATTR(MONTH([Order Date]))-WINDOW_MIN(MIN(MONTH([Order Date])))

Which will shift the minimum month to 0 and then go from there.

1 of 1 people found this helpful
• ###### 2. Re: Complex Date calculation_Custom Date levels

Hi Swaroop,

Many thanks for your response. Really appreciate.

I need some more help on this topic. I applied your logic and found I am getting few null value's for Month. We need to avoid Null and Month 0 from the display. But We can't exclude that. Our result should start from Month 1 and it should go beyond Month 12 if we have the data.

Another thing is we actually need to work with the discrete measure. PFA the Excel file and the new dashboard which is giving us the incorrect result.

Eg : as per the excel file for Identifier 10001003 the event timeline should be Month 1 - Event 6 , Month 2- Event 5, Month 3- Event 3, Month 4 - Event 8, Month 5 - Event 2 and finally Month 13 (Since beyond the Month 12 or next year from the first event )should show Event 4.

 Identifier Event Name Event Date 10001003 Event 6 29/01/2015 10001003 Event 5 01/02/2015 10001003 Event 3 15/03/2015 10001003 Event 8 19/04/2015 10001003 Event 2 31/05/2015 10001003 Event 4 11/01/2016

 10001003

We are almost there so someone could kindly let me know any tricks that should be really valuable.

• ###### 3. Re: Complex Date calculation_Custom Date levels

Hi Swaroop also to add in realtime when we applied this logic it gave us incorrect result for the dataset.

Please find the latest .twbx file where we have replicated this.

Please check the Patient ID 10001002 at the tableau file and the excel file. Both are not showing the correct result.

• ###### 4. Re: Complex Date calculation_Custom Date levels

Sorry forgot to attach the files. Excel file would give you more clarity.

• ###### 5. Re: Complex Date calculation_Custom Date levels

Somok,

Please look at Dashboard 3 of the attached to see if it is closer to your requirement.

I broke up the month calculation into a few parts:

[MinDate]:

WINDOW_MIN(ATTR([Event Date]))

[Month Calculation]:

DATEDIFF('month',[MinDate],ATTR([Event Date]))

[Month Label 2]

"Month "+STR([Month Calculation]+1)

On the Table Calculations, it looks like a "Compute using" or Table (Across)

works, but needs to be doublechecked.