5 Replies Latest reply on Aug 20, 2013 2:44 PM by Joshua Milligan

# How can 'DATEDIFF' formula give me different outputs everytime?

Sorry for being repetitive on this forum and I thought I had gotten the answer to this question last week but guess I was wrong. Could any of the experts help me explain how do I get different outputs but none of them what I want to see on the vizz.

I tried to do the following

1) 30-DATEDIFF('day',#July 21,2013#,#September 21,2013#) :- the aim of doing this was to see how many days out does my product sells and at what cost. It gives me one count -32

2) DATEDIFF('day',#July 21,2013#,#September 21,2013#):- this formula gives me 62 day output but again one single count.

My first question is how can I tweak the formula so I would be able to see all days leading upto that 62nd day. for example 1,2,3........60,61,62. Not just one figure like what I have now which gives me one count of 62.

My second question is datediff is a formula which will give me a count of days so lets say I want to add a reference line saying on September 20, 2013 is the day when my product was the most expensive, would I be wrong if i say that 61st day on the axis is that date or would it be the other way round i.e. 2nd day on the axis?

Third question is in my 1st calculation why would it give me -32 instead of 62 or -62 ?

I have attached my current vizz and any expert opinions are welcome and again sorry for being repetitive.

Regards,

Kiran

• ###### 1. Re: How can 'DATEDIFF' formula give me different outputs everytime?

Kiran,

The calculation DATEDIFF('day',#July 21,2013#,#September 21,2013#) will always give you the value 62 because there are 62 days from 7/21 to 9/21.  And you'll always get -32 when you subtract (i.e. 30 - 62 = -32).

If you are trying to determine, for each record of data, the number of days prior to Sept. 21, then try a dynamic formula like: DATEDIFF('day', [STARTDATE], #September 21,2013#)

That will give you the number of days out from Sept 21 for each record based on the start date value for that record.

You second question totally depends on what your axis is based on.  It could be the 61st day or the 2nd day.  What do you want it to be?  What kind of answer are you looking for from the data?

For example, here is a visualization of the number of items sold on each day up to Sept 21 starting with 60 days out:

• ###### 2. Re: Re: How can 'DATEDIFF' formula give me different outputs everytime?

Hey Josh,

I changed the formula as you suggested and re worked the vizz and I think I have it to a point where it looks similar to what you did. Please advise if it's not.

Would it help if I drag 'startdate' in the filters column so if I want to manipulate the number of days by dates I can do that or is it unnecessary according to you?

Thanks a ton Josh.

• ###### 3. Re: Re: Re: How can 'DATEDIFF' formula give me different outputs everytime?

Kiran,

You can filter however it seems most intuitive for you and your users.  I would recommend using either a date range on StartDate or a value range on Datediff.  Either of those will filter the fields as continuous values

(Right click them in the Data window to the left, under Dimensions, and select "Continuous".  The field will change from blue/discrete to green/continuous.  Then drag the green field to the Filters).

I've attached a workbook to show both options at once.  You'll probably want to choose one or the other.

Regards,

Joshua

1 of 1 people found this helpful
• ###### 4. Re: Re: How can 'DATEDIFF' formula give me different outputs everytime?

Yes I like the widget idea, I would prefer either one. Great, this is good stuff.

Thank you Josh, you made what looked to me a some what complex date formula and put it in a simple manner.

• ###### 5. Re: Re: How can 'DATEDIFF' formula give me different outputs everytime?

Kiran,