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

Using a formula result of current month in other formula of future months

Hello Gurus,

I have very simple calcalution to perform but I'm not sure how to achieve it.

Let me try to explain:

I have the following query:

Time(Months) in collumns.

Keyfigures /Formulas in rows.

Below table shows the result I want to achieve

         06.2008   07.2008   08.2008   09.2009  10.2009   11.2009
KF1        50        60        70        80        90        95
KF2        30        30        40        30        50        40
F1         ..        ..        30        ..        ..        ..
F2                             70        60        80        70

For the current month (in this case 08.2009) query calculates the difference of KF1 - K2 into formula field F1. This difference F1 shall now be added in current and all future months (09.2009, 10.2009, ...) to KF1 and output to F2 (F2 = KF2 + F1). Above table shows how it should look like. What I get is the following:

         06.2008   07.2008   08.2008   09.2009  10.2009   11.2009
KF1        50        60        70        80        90        95
KF2        30        30        40        30        50        40
F1         ..        ..        30        ..        ..        ..
F2                             70        30        50        40

F2 calculation works for the current month but problem is that the result of F1 in 08.2008 (30 = 70 - 40) is not available in the future months.

Is there any way to solve this within a query?

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 Aug 11, 2009 at 04:47 PM

    You likely have defined F1 as a combination of formula and RKF.

    Define another set of formula and RKFs to get a KF FC which is similar to F1 - with only one difference. For all RKFs being restricted for current month, while restricting set the constant selection (right click on the month selection for RKF and choose CONSTANT SELECTION).

    If you put FC in another row in this example, it will show a constant value of 30 for all columns. You can use this FC instead of F1 in calculation of F2 to get what you look for.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 12, 2009 at 12:25 PM

    Hi UM1970.

    I am not sure if you can create this kind of dynamic change of KF's at all, but maybe you can succeed using Cell Definitions in the query. You would have to fix the columns for each month and I am not sure you can model the condition to only add if the month is larger or the same as the current month, but you will be able to keep the amount that has to be added constant:

    Add a RKF using an exit variable that gets the current month (there should be a standard one around) and hide it. This will let you calculate the diff to be added and keep it constant in the calculations.

    Next, you need the 12 columns; one for each month. Create another RKF using an exit variable that get's the first month of the current year (dont think there is a standard one around, so you have to code it...). Copy this RKF and add an offset two the variable until you got 12 columns (up to +11).

    Create a formula variable that reads the first two characters of the value for current month (mind that the internal format is fx 200908, so you have to use an offset). This will give you the numeric value of the current month.

    Go to cell definitions and define a reference cell. In this, you calculate the difference between KF1 and KF2, using the current month column, that you will hide when you are done.

    Now in the row for F2, you do the calculation of adding the reference cell to KF2 cell of each of the 12 columns. To implement the logic of only adding the reference cell value if the month is larger than or equal to the current month, you can use the boolean operators and the formula variable you created above, which I think are available for calculations in cell definitions:

    In column 1 (which will always be January, so month = 1):

    ( 'formula variable' <= 1) * ('cell for KF2 value of column1' + 'reference cell')

    In column 2 (which will always be February, so month = 2):

    ( 'formula variable' <= 2) * ('cell for KF2 value of column2' + 'reference cell')

    In column 3 (which will always be March, so month = 3):

    ( 'formula variable' <= 3) * ('cell for KF2 value of column3' + 'reference cell')

    etc.

    That should work, I think, but haven't exactly tried it in detail 😉

    regards

    Jacob

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Jacob,

      As Ajay's solution now is working I will not try yours which sounds workable, too. If possible I try to avoid using cell definition in a query as from my experience the queries are hard to maintain/change.

      Thanks anyway!

      Regards,

      Ulrich

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.