Skip to Content
author's profile photo Former Member
Former Member

Variable CalMonth Offset and simultaneous Calyear Filter

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Nov 30, 2007 at 06:57 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 03, 2007 at 09:22 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.