Result of Interval is text and not number

[#1]

Hello;

I am trying to get the average of month per contract.

This is the formula used:

num#((num#(round((num#(Interval([Contract End Date]-[Contract Start Date],'dd')/365)*12))))/Count (DISTINCT Contract))

Even using the num#, it never provided a number in the total of the expression.

What to do in this case?

Thanks

Kristel.

[Anonymous]

Can you provide some sample data for Contract End Date, Contract Start Date, and Contract?

[jonathandienst]

Kristel

Unpacking your expression, it looks like you are trying to calculate a fraction of a month (days/365*12), divided by the contract count. In that case, this will work:

((Floor([Contract End Date])- Floor([Contract Start Date])) / 365 * 12) / Count(DISTINCT Contract)

But in a a text box, this will work only if you have made selections such that there is only one possible value each for [Contract Start Date] and [Contract End Date].

If you are using it in a chart, then the dimensions must be such that each line only has one possible value each for these two fields.

Otherwise, you will need some sort of aggregation ,like Max() or Min() for these expressions.

Hope the helps

Jonathan

[#4]

The end date would be something like 2013-30-06

The start date would be something like 2013-04-01

A contract is just a number 41258987

Hope this is helpfull.

[jonathandienst · Accepted solution]

After taking a second look you just want the average, so my previous posting is not correct. To calculate the average number of months per contract across all contracts, assuming each contract has a [Contract Start Date] and [Contract Start Date], then it is this simple:

=Avg(Floor([Contract End Date]) - Floor([Contract Start Date])) / 365 * 12

Jonathan

[jonathandienst]

If the dates do not have a time value, then the Floor()'s are not needed

[jagan]

Hi,

((Num((Interval([Contract End Date]-[Contract Start Date],'dd'))/365) * 12)/Count (DISTINCT Contract))

If the above not works then check the value of this in text box

=Interval([Contract End Date]-[Contract Start Date],'dd'), if you are not getting any value and check the date formats, ie.,

Date format of Today() and ([Contract End Date]-[Contract Start Date] are same.

Regards,

Jagan.

[#8]

The formats are different.

today = 2-Oct-2013

end and start = 2013-05-01

So, as you mentioned the provided formula is not working.

[jagan]

Hi,

Convert the date formats like this

((Num((Interval(Date(Date#([Contract End Date], 'YYYY-MM-DD'))-Date(Date#([Contract Start Date],  'YYYY-MM-DD')) ,'dd'))/365) * 12)/Count (DISTINCT Contract))

Regards,

Jagan.

[#10]

Yep;  this is exactly what i was looking for.

Thanks