Variable CalMonth Offset and simultaneous Calyear Filter

[Former Member]

Hello,

I used a variable (user entry) to determine Calmonth, and then I used offsets of the variable to restrict keyfigures and display them in 4 colums, one for each of the 4 quarters preceding calmonth as follows:

1) calmonth-11 to calmonth-9

2) calmonth-8 to calmonth-6

3) calmonth-5 to calmonth-3

4) calmonth-2 to calmonth

The problem is that I want also to restrict the keyfigures (and/or the hole query) to the year of calmonth (I dont want to take into accounth the values of the keyfigures if the offset 'falls' the year before).

Example:

Calmonth = 06.2007

Then:

1) calmonth-11 to calmonth-9 = 07.2006 to 09.2006

2) calmonth-8 to calmonth-6 = 10.2006 to 12.2006

3) calmonth-5 to calmonth-3 = 01.2007 to 03.2007

4) calmonth-2 to calmonth = 04.2007 to 06.2007

I only want to take into account the values of the key-figures for 2007: 3) and 4).

So columns corresponding to 1) and 2) should be empty...

To do so, I have a variable (exit) for calyear that takes the value of the year of calmonth.

I then additionally restricted the keyfigures with my calyear variable, and I also put the calyear variable in the Filters of the query.

I also put caracteristic Calyear on top of the columns (before the keyfigures).

My problem is that on the layout, in the example below, I have all the colums for 2006 and all the columns for 2007, (at least filled with the good values: 2006 has values only for 1) and 2), and 2007 has values only for 3) and 4)).

But I don't want to have all the 2006 part on my report (having 2006 makes me have a lot of additional rows that are empty on 2007)...

Why doesn't my simultaneous restriction on keyfigures (calmonth with offsets AND calyear) work?

Why, if I have a filter on calyear : 2007, I have columns corresponding to 2006?

Thanks a lot for your help!

Andrea

[Former Member · Accepted solution]

Even though you have a filter on 2007, you will have columns corresoding to 2006 but you will not have values in those. All of them will be blank. If you don't want to see those columns, you will have to change the way you structured your query.

[Former Member · Accepted solution]

You can also try suppressin blank rows and columns.

[Former Member · Accepted solution]

Thanks for your answer. In fact, my problem is not just having the comlums for 2006 but the fact that <u>they're not blank</u>... I've got values on it and that makes my results wrong (For example, I created a column using a SUMGT of another column/keyfigure, and it seems to take into account values of the keyfigure for 2006 AND 2007 => in the the 2007 part of the query. I just want a sumgt of 2007 in 2007...).

[Former Member · Accepted solution]

Your restriction should work. Your exit may not be working properly. Check it again. I did some thing very similar and it works for me.

[Former Member]

Andrea,

Try populating the quarter values based on customer exit only. In this case even though you have done restrictionsw with diff variables, the offsets do not work because of which you are getting values in 200 also. Try making the restrictions more specific using customer exit only.