PeriodsToDate wery strange function

[en1]

Hi!
Somebody can tell me how it works.

[Francis aka khayman]

can you give more information?

[MFGF]

Hi!
Somebody can tell me how it works.

Here is the dialog from the "Information" pane when you select that function in your expression editor:

periodsToDate ( level , member )
Returns a set of sibling members from the same level as "member", as constrained by "level". It locates the ancestor of "member" at "level" and returns that ancestor's descendants at the same level as "member" (up to and including "member"). Typically used with a time dimension. This function appears in the Rolling and Moving Averages interactive sample report.
Example: periodsToDate ( [great_outdoors_company].[Years].[Years].[Year] , [2004/Mar] )
Result: Returns values for [2004/Jan], [2004/Feb], [2004/Mar]

Also see page 772 of the Report Studio user guide for details:

http://public.dhe.ibm.com/software/data/cognos/documentation/docs/en/10.2.2/ug_cr_rptstd.pdf

MF.

[en1]

thx for your replies!
it turned this function result depends on sorting in time hierarchy.

[MFGF]

thx for your replies!
it turned this function result depends on sorting in time hierarchy.

Yes - it does. It wouldn't make sense to have your time members in unsorted order, though. It would be completely confusing for anyone trying to use it.

MF.

[en1]

how can i use it if time hierarchy sorted desc (by yars)

[MFGF]

how can i use it if time hierarchy sorted desc (by yars)

Depends what level you want to use it on? If it's the Year level, it makes no sense to use it. If within each year the quarters/months etc are in a normal ascending order, then that's fine.

Why would you create a year level with the years in descending order? It sounds like a very confusing thing to do...

MF.

[en1]

yes. it is problem. hierarchy already created an sorted on year level desc. next problem PeriodsTodate not work at years (1 before main top) level. i select 2012 year and  PeriodsTodate result is 2012.despite that hierarchy sorted desc PeriodsTodate in this case have to return 2013,2014 etc years but dont :( another hierarchy with levels above year work (return 2013,2014 etc).

i try to invert this function results but it difficult because in year dates sorted asc but in year desc. and when i use function for day level  PeriodsTodate return all days since begin of the year and all days from years after this year/

maby are some varians to invert it results? any idea?

[MFGF]

yes. it is problem. hierarchy already created an sorted on year level desc. next problem PeriodsTodate not work at years (1 before main top) level. i select 2012 year and  PeriodsTodate result is 2012.despite that hierarchy sorted desc PeriodsTodate in this case have to return 2013,2014 etc years but dont :( another hierarchy with levels above year work (return 2013,2014 etc).

i try to invert this function results but it difficult because in year dates sorted asc but in year desc. and when i use function for day level  PeriodsTodate return all days since begin of the year and all days from years after this year/

maby are some varians to invert it results? any idea?

Sounds like your wonky sorting is totally confusing things. The best idea is to order the years chronologically, then it's really straightforward.

MF.