Macro using SUM function with Row & Column Relative

[ruchi_das2]

Hello Gurus,

I am trying to write a macro for the following scenario and below that is what i have so far.

	Col 1	Col 2	Col 3	Col 4	Col 5	Col 6	Col 7	Col 8	Col 9
KF1			380		200
KF2	200	100	50	180	50	50	50	50

When macro reads value in KF1, it should sum up the values of KF1 & KF2 until that column.

If the sum(KF2) <  sum(KF1) value, turn KF1 Cell to RED.

IF

Condition

KF1 > 0 (Frm Col1)

AND

(

Function SUM (KF1 Col1, KF1 Col9)  [ Row Relative & Column Relative set for TO]

-

Function SUM ( KF2 Col1, KF2 Col9) [ Row Relative & Column Relative set for TO]

> 0 )

Attribute ( KF1) = RED    

ENDIF

I have not checked the row relative & column relative for the from row for both Key Figures since i want the calculation to start from Col1 for each iteration and progress the TO based on iteration column. The macro just turns all KF1 cells which have values to Red. Tried mix and match with Row/Column relative but nothings working so far. Appreciate your inputs.

Regards

AK

[RahulHanda]

Hi Abhishek,

If I understood it correctly, you are trying to compare the cumulative of two key figures over a period of time and see in certain bucket if it is greater then the other key figure.

Correct me if I am wrong, if not then you would need to take help of auxiliary table to have row with cumulative of both and then compare these cumulative. If one cumulative is greater then the other turn KF1 red.

Example:

Aux 1 = KF1 (Col 1)

Aux 2 = Aux1 + KF1 (Col 2)

Aux 3 = KF2 (Col 2)

Aux 4 = Aux 3 + KF2 (Col2)

IF

Aux 1 > Aux 3, then KF1 (att.) = Red, and

Aux 2 > Aux 4, then KF1 (att.) = Red.

Do share the results.

Regards

Rahul

[ruchi_das2]

Hi Rahul,

Thanks for the suggestion. However, I could achieve this by using the time series Key Figures itself. I had to set the from date equal to the To date and set row/column relative for the TO Date.

Regards

AK