[Solved]Setting Current Period for Lunar Fiscal Calendar when there is no data...

[SueC]

Just found this forum today.Ã,  I certainly hope it's more active than the support site forum, I need a place to mingle and get help. :-*

I have a first of the month situation where sales invoicing is stopped until the previous month is closed.Ã,  My cube is based on invoiced sales records with the date dimension (lunar 5-4-4 calendar) driven off of the posted date.Ã,  "Sets the current period" is set.

I've set up a special date category called "3-week report " which groups the last three completed weeks (current week -1, current week -2,Ã,  current week -3).

Last Monday (11/07) was the first of fiscal month 11, and the beginning of week 45 (fiscal week starts on Monday).Ã,  The cube was built at 3:00 am. and the latest date in the database was month 10, week 44.Ã,  Maybe you can see where this is going....

If, on Monday, 11/07, the user pulls the special date category "3-week report" into PPWeb, weeks 43, 42 and 41 are displayed NOT week 44 as the user expected.Ã,  The cube remains with the current week set at week 44 until data comes through for week 45.

I understand why this happens but there has to be a way to have the cube set the current period based on the system date rather than based on the data.Ã,  Is there such a thing?Ã,  I've played with setting the current period manually but I'm not getting anywhere.Ã,  Can anyone offer some help?

Thanks

[dwood09]

Hi SueC,

Most of our financial cubes are built off date values representing "Current Month" and the like, as opposed to using fact data.  Ensure that your fact data sources have the "Sets Current Period" turned off in the General Tab, and add a new data source with the value you would like to set the current period.

It may require a bit of playing around, but it certainly works.  let us knwo how you go.

cheers,
DW

[SueC]

DW,

Thanks for your reply.  I've tried several variations this morning.  The only one that has returned a good result is to include an xls data source.  I've tried adding a second iqd; adding column to the existing iqd.  I've tried putting that column at the beginning of the field list and at the end of the field list.  None of those options worked, but it seems like they should have.  As long as it's the last date field transformer sees it should use that one to set the period, right?

Sue

[SueC]

I found the 'trick' here.  The trick is to add a separate datasource with a single column setting the date to 'today' (or whatever date you want to set as the current period).  Put that datasource as the LAST one in the datasource list so that date field is the LAST record transformer reads.  Make sure that last datasource is the only one with "Sets Current Period" turned on in the General Tab under Properties.

The problems I was having finding a solution didn't so much depend on what type of field it was (iqd, calculated, xls, etc.) but WHEN it gets read.  The only way to make sure it's the last date read is to put it in it's own datasource.

Hope this helps anyone else who encounters the same problem. 

Sue