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?
2 Answers
-
Best Answer
Former MemberPosted on Aug 11, 2009 at 04:47 PMYou 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
-
Former MemberPosted 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
Add a comment