date difference problem

[venkiatmaruthi]

what will it return if we have the below expression in cognos data item.
Both are dates.

Start date - end date

I am not able to keep the above dataitem in the graph. what could be the possible reason?


thanks.

[Lynn]

Subtracting the dates would give you a time interval (e.g., 3 days 12 hours 6 minutes 47 seconds).

You didn't explain what the problem with the graphing is. I did a quick test and was able to use the interval as the default measure on a bar chart. The data item has an aggregate property of total. The y-axis scale looks like it is in milliseconds but I didn't play around with the axis properties to show an understandable value.

[venkiatmaruthi]

Thanks Lynn,

here is the problem....
I am doing a migration work from 8.2 to 8.4.1. In one of the reports, I am getting the error
Parse error near the position 24 "Cast(NULL as INTERVAL)"

When I checked the report, I noticed a Category(X-axis) have the TIMESPAN dataitem.
that dataitem has the following calculation

case
when cast(varianceofhours,real) <4 then '<4 hours'
when cast(varianceofhours,real) >=4 and cast(varianceofhours,real) <=12  then '4-12 hours'
when cast(varianceofhours,real) >12 then 'over 12 hours'
else NULL
end

here varianceofhours is another dataitem. varianceofhours is having another calculation

I have timedelta as specided above have the interval. (startdate - Enddate)
he has extracted days, hours, minutes and seconds from timedelta and converted to hours manually.
so varianceofhours contians the hours. from this we got TIMESPAN with the above calculation.

This TIMESPAN is giving the error in 8.4.1. However the same logic is being worked fine in 8.2.


Please help me.

Thanks

[Lynn]

Null is null is null.

I've never run across casting a null to anything. Since the case logic you describe generally wants the same datatype for all possible outcomes maybe he just got carried away.

I would try taking it out and just use "null"

[venkiatmaruthi]

Lynn,

I tried different options on that.

I used 'Null' and '' (empty string) and also 'a' (dummy value). But those were not worked.

If I remove the varianceofHours and replace any numerical number like 8.2, 16, 100... etc it works fine.

If I remove TIMESPAN from Category list in graph, the logic is getting through the validation.

So that's why I moved to up the levels one by one. I used varianceofhours directly in graph since it's a numeric. that's didn't get through the validation.

Then I moved to timedelta (uppder level to varianceofhours)  and used that. but no luck.

then again, moved to one level up that timedelta=startdate-enddate. so I used startdate and the report passed through the validation. and similarly for enddate as well.

so now the problem exist at the timedelta I believe.


Hope this helps. I am running out of ideas and also I am new to Cognos.


Thanks.
Venkat

[Lynn]

Hi Venkat,
It sounds like you've got a lot going on there and I'm not coming up with any useful suggestion.

As general advice I'd say do what you indicated...rip out small pieces and build it up a little at a time to troubleshoot. Remember that conditionals require all possible outcomes to evaluate to a consistent type, so maybe there is another branch in the case that is actually the problem?

When posting to the forum, it is helpful to include any specific error messages or symptoms rather than that it didn't work or it doesn't validate. You mentioned the parsing error in the earlier post, so not sure if you're still getting the same thing? Maybe including the complete error and the problematic expression is a way to get better advice from members here.

Lynn

[venkiatmaruthi]

Thanks Lynn.

Usually, I send the full errors and descriptions. yesterday I was in frustration mood as it's not working.

I am getting the following error___

QE-DEF-0459 CCLException
QE-DEF-0260 Parsing error before or near position: 24 of: "cast( NULL as interval)"
QE-DEF-0261 QFWP - Parsing text: cast( NULL as interval)
QE-DEF-0260 Parsing error before or near position: 24 of: "cast( NULL as interval)"
QE-DEF-0261 QFWP - Parsing text: cast( NULL as interval)

If I remove the dataitem TIMESPAN. it is working fine.

[venkiatmaruthi]

Another observation is Tabular data is running fine and getting the result for all cases.

However if we keep that TIMESPAN column or upper levels till TIMEDELTA in Category column, we are getting the above error.

[Lynn]

Maybe posting the expression that is complaining would help?

[venkiatmaruthi]

Thanks Lynn.

Finally make it.
I played on the Query aggregations for each data item.
All the data items in the query have Automatic aggregation. so removed for some of the Dimensions and kept for neccessary dimensions and measures.
Thanks a lot for your great Help Lynn.