running-difference function with date fields

[cygnex]

I have used this function before and understand how it works but I am getting incorrect results when using date fields.  Someone mentioned that the dates need to be converted to Julian format but I do not know if that is possible at the report level or database level.

Has anyone been able to use the running-difference function with dates?

[tiga123]

Hi,

I've been investigating the running- statements for some time, and some of them causes wrong results when combining it with other columns/rows.

I don't know were you use this functionality for, if it is a standard report (crosstab) you can try to use the "For" option.

Running-difference([measure] for [year])

          |  2008  | 2009 |
week 1     1
week 2     3
week 3     3

but when adding extra dimensions to the crosstab it can result again in wrong results, because of the predefined level. (year in this case)

another solution which is working is using a cube, then afterwards in report studio use the periodsToDate statement. this will take some extra work, but it is always correct. ofcourse you need to add a measure into your cube where you can calculated your running difference.

[blom0344]

I have used this function before and understand how it works but I am getting incorrect results when using date fields.  Someone mentioned that the dates need to be converted to Julian format but I do not know if that is possible at the report level or database level.

Has anyone been able to use the running-difference function with dates?

Each RDBMS has it's own solution for creating the julian format of a date. So ,the answer is , that it is possible , but the exact expression depends on the database that you use..

[tiga123]

Each RDBMS has it's own solution for creating the julian format of a date. So ,the answer is , that it is possible , but the exact expression depends on the database that you use..

If you set your query type to "native" or "Pass-through" you are right. standard cognos has it own language in that case always this syntax works: Running-difference([measure] for [year]).

running-difference can also work for a product, Running-difference([measure] for [year],[product]).

periodsToDate statement is running on mdx, so there is no database anymore.

[blom0344]

My answer was to the question on how to define the julian date. If you create this as an object (derived field) in the database you can then use it for easier date calculations.
It is quite common to have a date lookup table that has a julian date for this purpose.

It was not the intention to use native or other SQL to perform this from the report itself..