Cognos Powerplay - 2009 date issue

Hi mark,
thanks for comments so far, yes maybe is because the first of jan fell on a thursday but i think its also likely to be something in access.

Ok ill try to post the query if i can soon.

OK its difficult to post the database/query but ill post an excel export of the query data. and another screenshot of powerplay with the date 12/01/09 expanded.

Powerplay uses the data from access (that you can see in the exported excel file) I think its the "original invoice date" filed  thats makes up the date lables in powerplay and the invoice date is the one with the actual data that powerplay uses, it seems a bit confusing i think and cant work out exactly what its doing yet but i guess theres a reason why it was setup like this (wasnt me who originally set this up).

The "invoice date"  field seems to do a -7 days subtraction in access from the original invoice date.

OK,

Looking at your posts the issue is in your Access Query because your three date columns at the end are all over the place.  can you view the query in SQL view (open the query in design view and then select "SQL View" from the view menu on the tool bar) and then copy and past the SQL so I can take a look at the make up of the query.

there seems to be some sort of date calculation being done with what looks like the invoice date to get your WEEK_DATE column and Ill bet the turn of the year has thrown it out for some reason (Similar things have happened to me on a few reports at work over the lase few weeks!).

If you can get the SQL form of the query posted I might be able to help.

I will be logging off for the night in a few minutes but if you can get the SQL up I'll try to take a look at it in the morning.

Hope this helps,

Mark.

HI,

Ok ill post the SQL code in the Query, yes if your able to take a look that would be great, thanks for your help

SELECT invoices.[Invoice Date], customers.[Rep Code], customers.[Area Code], invoices.Partcode, invoices.[Cases  ], parts.[Br Unit], [br unit]*[cases] AS Brushes, parts.[Con Unit], [Con Unit]*[Cases] AS Consumer, invoices.[Item Price], invoices.[Line Total], Costs.[Std Cost], [Line Total]-([Cases]*[Std Cost]) AS [Std Margin], Costs.[Prime Cost], [Line Total]-([Prime Cost]*[Cases]) AS [Prime Margin], invoices.Customer, invoices.[Cust Branch], parts.[Parts Description], comm2.[Sale Type], comm2.[Comm2 Description], comm3.[Comm3 Description], customers.EUK, customers.[Customer Name], reps.[Rep Name], areas.[Area Name], Branches.Location, Trim([parts]![partcode])+" "+[parts description] AS [Parts abbrv], [customers]![customer]+" "+[Customer name] AS [Customer abbrv], Str([WEEK_DATE]) AS TXT_DATE, invoices.[Original Invoice Date], IIf([purchased]=True,"Purchased","Manufactured") AS BIFLAG
FROM (((((((((invoices INNER JOIN parts ON invoices.Partcode = parts.Partcode) LEFT JOIN comm2 ON parts.Comm2 = comm2.Comm2) LEFT JOIN comm3 ON parts.Comm3 = comm3.Comm3) LEFT JOIN customers ON invoices.Customer = customers.Customer) LEFT JOIN Costs ON (invoices.Partcode = Costs.Partcode) AND (invoices.Year = Costs.Year)) INNER JOIN Branches ON invoices.[Cust Branch] = Branches.[Cust Branch]) LEFT JOIN reps ON customers.[Rep Code] = reps.[Rep Code]) LEFT JOIN areas ON customers.[Area Code] = areas.[Area Code]) INNER JOIN [COM-PERIOD_DATA] ON invoices.[Original Invoice Date] = [COM-PERIOD_DATA].DATE) INNER JOIN [COM-WEEK_DATES] ON ([COM-PERIOD_DATA].WEEK = [COM-WEEK_DATES].WEEK) AND ([COM-PERIOD_DATA].PERIOD = [COM-WEEK_DATES].PERIOD) AND ([COM-PERIOD_DATA].YEAR = [COM-WEEK_DATES].YEAR)
WHERE (((invoices.[Original Invoice Date])>=#1/1/2004#));
                                              

1:
2:
3:

Ive found a macro that is set to run overnight (every day) that appears to do the following calculation on the "Invoice date" field

Criteria:    >=#12/01/04# And <=#31/12/10#
Update to [invoice date]-7

seems to subtract 7 days from the original invoice date, maybe i need to modify this calculation to get the data in 2009 in the correct weeks. found a comment in the macro thats says "Re-date invoices -7 days for 2004 53 week problem" seems like this could be the key to fixing the issue.

Hi Kevin,

The code you have posted does not seem to be the code that generated the Excel data you posted?

Can you double check.

I will be off now till the morning but I will check back first thing.

Mark.

Ah,  Just seen your second post.

It does seem like this may be something near to the issue.

I have had a report fail during the switch between 2008 and 2009 due to the last few days of 2008 being classed as week 53 and the first few days of 2009 being classed as week 1.

In the report I had an issue with I put an error trap in which changed anything from week number 53 in year 2008 to be classed as week 1 in 2009 (therefore moving everything into the same week number).

Post what you can find an I'll try to help out in the morning if your still stuck.

The exported data in excel was from the original query but i disabled some fileds from not showing and filtered the dates more.

Below is SQL from the version of the expoerted Exel data.

SELECT invoices.[Invoice Date], customers.[Rep Code], invoices.[Item Price], invoices.[Line Total], Costs.[Std Cost], [Line Total]-([Cases]*[Std Cost]) AS [Std Margin], Costs.[Prime Cost], [Line Total]-([Prime Cost]*[Cases]) AS [Prime Margin], reps.[Rep Name], Str([WEEK_DATE]) AS TXT_DATE, invoices.[Original Invoice Date], [COM-WEEK_DATES].WEEK_DATE
FROM (((((((((invoices INNER JOIN parts ON invoices.Partcode = parts.Partcode) LEFT JOIN comm2 ON parts.Comm2 = comm2.Comm2) LEFT JOIN comm3 ON parts.Comm3 = comm3.Comm3) LEFT JOIN customers ON invoices.Customer = customers.Customer) LEFT JOIN Costs ON (invoices.Partcode = Costs.Partcode) AND (invoices.Year = Costs.Year)) INNER JOIN Branches ON invoices.[Cust Branch] = Branches.[Cust Branch]) LEFT JOIN reps ON customers.[Rep Code] = reps.[Rep Code]) LEFT JOIN areas ON customers.[Area Code] = areas.[Area Code]) INNER JOIN [COM-PERIOD_DATA] ON invoices.[Original Invoice Date] = [COM-PERIOD_DATA].DATE) INNER JOIN [COM-WEEK_DATES] ON ([COM-PERIOD_DATA].WEEK = [COM-WEEK_DATES].WEEK) AND ([COM-PERIOD_DATA].PERIOD = [COM-WEEK_DATES].PERIOD) AND ([COM-PERIOD_DATA].YEAR = [COM-WEEK_DATES].YEAR)
WHERE (((invoices.[Original Invoice Date])>=#12/1/2008#));
                                              

1:
2:
3:

ive just tried to change the invoices from 5th january 2009 and onwards ie >04/01/08 to subtract 10 days from the "original invoice date" for the "invoice date" (powerplay seems to use the "invoice date" as the data" instead and ive modified the macro which runs overnight to -10 instead of -7

because i think 2009 has 3 days to many at the end.

Update to [invoice date]-10
i will run a test powerplay cube generation overnight and will see how powerplay looks in the morning and will let you know the results, im not sure if this will fix the issue but thought id give it a try.

week 1, period 1 is meant to start on 1st january 2009 for us  (well for our comapny accounting calender anyway) week 52, period 12, week 5 is meant to run from 22nd december 2008 to 31st december 2008 for our accounting calender that we go by.

I think I see what this is doing now but I'm not sure modifying dates is the best way to go about it.

See if your fix works and if it does then great.  If not, it ma be worth looking into changing your query slightly to modify the invoice dates "on the fly" when querying them to convert them into a first day of the week date.

I'm not 100% sure exactly how you would do this yet but I have done some date maninpulation in access before now.  So if your fix doesn't Work, give me a shout and I'll try to come up with something you could put into the query.

Good Luck!

Mark.

OK,

Here is what I have so far...

The following in access

DateSerial(DatePart("yyyy",[date1]),DatePart("m",[date1]),(DatePart("d",[date1]))-(IIf(DatePart("w",[date1])=1,6,IIf(DatePart("w",[date1])=2,0,(DatePart("w",[date1])-2)))))

Will take a date (in my example [date1] which you can replace with your date column name) and converts into into a new date which represents the Monday of the week that date falls in.

This still does not fix your problem I know.. but as soon as I can put some sort of error trap in to sort the end of year bug this could be used all the time without having to edit dates each night.

leave it with me for a while and I'll try to come up with something.

Hi Mark,
sorry about little late reply

It didnt seem to work properly last night, data for the 8th and 9th is showing in under week 2 on powerplay when it should be in week1.

Your suggestion sounds like a good idea.

Theres also 2 lots of lables in powerplay for 12/01/09 and im noit sure why but trying to find out now.
see screenshot.

the query in access that powerplay uses is a select query/non updateable query.
Yes ideally i dont want to edit dates each night, although there is already the macro i mentioned that runs each night that uses the invoice date column to subtracts from the "original invoice date" (date label in powerplay) that i changed last night to subtract -10 instead of -7. Powerplay seems to use the "invoice date" column  to represent the actual data it shows, hope that makes sense.


 

OK,

Not sure what is going on with your duplicate dates and we need to fix that.

I do now have a line of which will take your original invoice date and change it into what I think will be the correct week date.

i.e. the last few days of 2008 will be pushed back to 22/12/2008 and your beginning of 2009 should be reported correctly and the code is as follows:

IIF(DATEPART("yyyy",(DateSerial(DatePart("yyyy",[Original Invoice Date]),DatePart("m",[Original Invoice Date]),((DatePart("d",[Original Invoice Date]))-(IIf(DatePart("w",[Original Invoice Date])=1,6,IIf(DatePart("w",[Original Invoice Date])=2,0,(DatePart("w",[Original Invoice Date])-2)))))-(IIF(DATEPART("ww",[Original Invoice Date])=53,7,0)))))< DATEPART("yyyy",[Original Invoice Date]), (DateSerial(DatePart("yyyy",[Original Invoice Date]),1,1)),     DateSerial(DatePart("yyyy",[Original Invoice Date]),DatePart("m",[Original Invoice Date]),((DatePart("d",[Original Invoice Date]))-(IIf(DatePart("w",[Original Invoice Date])=1,6,IIf(DatePart("w",[Original Invoice Date])=2,0,(DatePart("w",[Original Invoice Date])-2)))))-(IIF(DATEPART("ww",[Original Invoice Date])=53,7,0)))

Lengthy I know!

But it should work and you should be able to test it on your source data to make sure the original invoice dates are being converted into the correct date.  If this works, you can then get rind of your macro  and change your source query to sue the code above to generate the date field for your reporting software.

Hope this helps,

Mark.

Hi Mark,
so you think the main issue is the last few days of 2008 thats causing the issue?, the last 3 days maybe?.

wow that is a lengthly bit of code thanks for this. I've just tired to insert this into a new column in the query and it says "the expression you entered is missing a closing parenthesis, bracket (]), or vertical bar (|)." and not sure whats missing in the code, any suggestions?

I guess this code needs to be setup on a new field, or will it be ok to use on the "invoice date" field, dont really want powerplay to reference any new fileds with a different name.

At present all the "invoice dates" from 5th January 2009 are -10 from the "original invoice date"
and before 5th Jan all the "invoice dates" are -7 from the "original invoice date"

From which date exactly should that code start to take effect and adjust invoice dates?, from late 2008?

It should not matter,

I have written the logic of the code to look at the original invoice date for any day of any year.  It should look at the date and convert it to the Monday of the week the date falls in unless one of two thing happen,

Option one, the week number is 53 (i.e. it is the last couple of days in the year and they need to be pushed back to the previous week).  I this case it will return the Monday of the previous week so 29/12/2008 returns 22/12/2008

Option two, the date is in the first week of the new year but the Monday of that week was in the previous year. for example 01/01/2009 is a Thursday but for your report you need the data from the 1st to the end of the week to be classed as 01/01/2009 even though it is not a Monday. In this case it will return the 01/01/yyyy where yyyy is the year of the invoice date.  So in our example 02/01/2009 returns 01/01/2009.

Hopefully this would give you the dates you require for any year from any time period.

Hope this makes sense.

Mark.

Ok i added one more extra bracket at the end of the code and it worked ok now, i see what you mean it makes all invoice dates fall under a monday, that looks great.

I guess i can remove the exsisting "invoice date" field (data is stored in a table) and like you say stop the macro that runs overnight that actually creates these dates in the "invoice date" filed by subtracting days from the "original invoice date" field.

and instead have use your code on a new "invoice date" filed. Ill give it a try soon a re-genearte the powerplay reports - it can take a little while for powerplay to process all the data, so ill let you know a liitle later the results.

Yeah,

Sorry about the bracket, I forgot to add that in when I pasted the text into the comment box.

Hope it works out.

Mark.

Hi ok powerplay has updated but its not correct still, its strange how it looks.
some dates are now showing under 19/01/09, im trying to work out what its done but not sure right now :-(

In powerplay the year is setup as a lunar year, i just thought perhaps i could try changing this to a 4,4,5 week month as our weeks are usually 4,4, 5 weeks apart from some weeks which are longer (such as the first week in jan 2009 and the last week in december 2008) so i guess this might not reolve the issue. Theres a few other options in the drop down "date function" list such as calender year but the other options i dont feel are suitable.

see screenshots.

Sorry mate, I'm not sure what to try next.

I have never used powerplay and so would not know where to start with changes on that side.

Double check that the data coming out of access seems to have correct dates (i.e. no transactions for dates after today) before you start making changes to these settings.

If it is an Access issue, I might be able to help some more, otherwise you might need help from someone who has experience with this software.

Good Luck!

Mark.

Ok thanks, annoying as 2004 and 2005 appear to be working just fine using your code but then from 2006 its doesnt seem to work - its out of sync.

I think the data in access is the key to fixing this.

I would suspect my code rather than Access!!

Could you post some sample dates that do and don't work, showing the date passed to the query and the date my code spits out and I'll see if I can fix it.

Mark.

hm maybe but im quite confused with this at present on how exactly its organizing the dates.

Ok ive attached a zip of some data comparisons, Overall totals in 2004 & 2005 seem to be the same although your code has moved some dates into different places making sub figures for sales people different. shown coparisons for 2005, hope this is of some help? or would you need to see some more data?

Thanks

Any chance of the raw data before it hits Powerplay?

I don't need any financials, just literally [Original Invoice Date], [Reported Invoice Date], [My Calculated Date]

With these three columns, I might be able to see where I am going wrong.

Mark.

Ok ill get theis to you shortly

Ok attached is a local copy of the access database, ive stripped out some of the data that you dont really need to look at such as part codes.

The query that powerplay looks at is named "sales query"

Hi mark,

seems like i removed some dates from earlier years prior to 2009 in the invoices table

the attached version has all the year data going back previous years

sorry

thought the attached of our accounting calender dates for 2004, 2008 and 2009, could be helpful.

as im sure your aware and can see 2008 ends in more days than normal (more than 7), makeing more days for the last week and the first week in 2009 has more days than usual.

There seems to be a few calultions someone has done at the bottom of the 2004 calender.  

Hmmm,

Looking at the Sales Query the Invoice Date Column does look right to me,  All the dates listed are Mondays and the adjustments at eh beginning and end of the years seem correct.  This may push the problem into the realms of Powerplay.

At this stage I have no idea where to start looking Kevin, Sorry!

Hopefully someone with some experience with this software might be able to pitch in.

Sorry again,

Mark.

Not sure if this is any help but take a look at this post:

http://businessintelligence.ittoolbox.com/groups/technical-functional/cognos-powerplay-l/date-issue-1403565

Ok no problem, yes your calculation seems to be working ok, I guess powerplay is doing something odd.

Some dates in powerplay seem to have slashes and other dates have the lablel: 20090101 (label should be original invoice date o think)

Thats fine no prob, I appreciate all your help, the code you've done could still be used if powerplay settings could be amended. I guess i may have to try to find an expert on powerplay to look into this issue.

Thanks

Ill have a read though that post.

Thanks

Ok,

Sorry I cannot be any more help!

If it is any use I could change the query to output the date in the yyyymmdd format rather than dd/mm/yyyy

Let me know if it will be worth a try.

Mark.

Hi mark,

Yes that would be worth trying, im desperate to get this issue resolved, so its worth tyring for sure.

Thanks

interestingly the original macro that runs overnight to do the -7 days subtraction calculation has this criteria >=#12/01/04# so prior to 12 January 2004 the "invoice date" seems to be exactly the same as the "original invoice date", must be a reason behind this, i didnt set this up in the first place and the person who did doesnt work for the company anymore :-(

maybe theres some logic behind this, leap year perhaps in 2004, cant work out why though

Hi Kevin,

Making one single statement generate the output got a bit tricky and Long.  So I have added a column to the example query you sent over and uploaded it below.

Hope this makes sense!

Mark.

No idea on the 2004 thing mate?

Thanks, think i can seen what the code is doing, just had a look but the invoice date shows as this: 1/090112 for 12/01/09 ie the dates have a an extra number and a slash, is there a way to amend this?

12/03/07 in the main invoice fiield shows as 3/070312 for example in the invoice date filed, an extra 3 and a slash is included.

ok i changed the code

from:
Invoice Date: Right([MAInvoice Date],4) & Mid([MAInvoice Date],4,2) & Left([MAInvoice Date],2)

to:
Invoice Date: Right([MAInvoice Date],2) & Mid([MAInvoice Date],4,2) & Left([MAInvoice Date],2)

and now format is: 090112

ill try to re-genrate the powerplay cubes to create the powerplay report now to see how it looks and will let you know

Sorry,

My mistake on the code.  Your fix should do the trick.

Good luck!

Mark.

Hi Mark,

Sorry about the late reply, been out of the office. In the end I asked an powerplay expert and they remotely fixed the issue. In the end he decided the best option was to create a new table in access that had the calender lables and got powerplay to use that table top base the year calender on.

Seeed like powerplay couldnt do the type of finance calender that we needed correctly without issues.
we tried various options in powerplay but none of them worked correctly.

Seems to be ok now, we just need to keep the new table in access up to date each year

Thanks for your help