Problem with query

[javier_alvaradorodriguez]

Hello all.

I need to do a query which shows me sales by month and week compared between actual year and last year. To do it I have created two new characteristics one for number of week (1- 53) and one for month,the month is not exactly the same as 0calmonth2 because a week is always asociated to only 1 month and it will be the one with more days on it; example: week 5 of 2005 (31/01/2005 to 6/02/2005) will be set to month 2, because it has more days in febreary than in januuary.

Now thr problem is that wen I set the query, it may happend that week 5 in 2004 in is month 1 and week 5 of 2005 is in month 2, so I have two lines where I should have only 1. I tried setting the month as constant selection for "last year" and it works fine, but the problem is that I only get the weeks which have data for "actual year", where I should get the whole "last year" and those of "actual year" which have data.

here is an example:

data:

year;month;week;sales

2005;01;05;10

2005;02;06;13

2004;02;05;5

2004;02;06;7

query:

month;week; sales actual year; sales last year

01;05;10;5

02;06;13;7

I know this is a tricky one. I hope someone can help me.

Regards,

Javier.

[Former Member]

Hi Javier,

You can try the following.

The main idea is to create some surrogate infoobject which will hold mapping info for different years and months.

So, the key field is a week number (as you want to see it in a report). Attributes: year and month (both compounding).

In the master data you’ll maintain figures like this:

Year Month Week

2004 01 01

2004 01 02

2004 01 03

2004 01 04

<b>2004 01 05</b>

2004 02 06

…

2005 01 01

2005 01 02

2005 01 03

2005 01 04

<b>2005 02 05</b>

2005 02 06

For your reports you’ll place this week number into rows, create two columns, both restricted by the year you need and the previous one. I think data should show up properly. Month you can show as a week attribute.

For some extra manipulations you can store the month as a separate char in the infoprovider. Besides direct feeding the month during load you can get its value in URs by lookup into mentioned above master data table.

Hope, it gives an idea.

Best regards,

Eugene

[Former Member]

Hello Javier,

I think the only way to solve this is to use the cell editor and define each cell manually with a calculation. This is of course quite awesome.

Another way could be to use a table interface class. But this is awesome as well.

Good luck!

Juergen