on 09-01-2014 6:10 PM
Hi experts,
I have the following report displaying KPIs in function of dimension TIME :
M1 M2 M3 T1 M4 M5 M6 T2 M7 M8 M9 T3 M10 M11 M12 T4 TOTAL
KPI 1 10 10 20 40 11 22 22 55 1 1 22 24 76 3 2 81 200
KPI 2 200 200 200 200 200 200 200 200 200 200 200 200
KPI 3 5 5 10
The KPI dimension contains members like: KPI1, KPI2, KPI3
The TIME dimension has the following structure :
TOTAL
T1
M1
M2
M3
T2
M4
M5
M6
T3
M7
M8
M9
T4
M10
M11
M12
- The values of KPI1 are entered.
- KPI2 = KP1 when TIME is TOTAL, which means 200 in our case
- KP3 = (KPI1 / KPI2) * 100
How can I retrieve the TOTAL value and copy it in KPI2 in order to calculate KPI3 using script logic ?
I tried this script but it doesn't work
*WHEN KPI
*IS KPI1
*REC(EXPRESSION =(SUM(DESCENDANTS([TIME].CURRENTMEMBER, 20, LEAVES))), KPI = KPI2)
*REC(EXPRESSION =((KPI1/KPI2)*100), KPI = KPI3)
*ENDWHEN
Thanks
Maha
Hi Maha,
Using script logic to calculate KPI like KP3 = (KPI1 / KPI2) * 100 is a bad idea. Please, use dimension member formulas instead:
In your case you have KPI1 and you want to calculate percent of total year as KPI3:
IIF([TIME].CURRENTMEMBER.PROPERTIES("LEVEL")="MONTH",[KPI1]/([TIME].CURRENTMEMBER.PARENT.PARENT,[KPI1]),IIF([TIME].CURRENTMEMBER.PROPERTIES("LEVEL")="QUARTER",[KPI1]/([TIME].CURRENTMEMBER.PARENT,[KPI1]),1))
Vadim
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Maha,
Not sure what is the input to your script and is it supposed to run in DEFAULT or DM Package, but to get SUM of the Year you can try something like [2014.TOTAL] in EXPRESSION.
Also, second REC in same WHEN/ENDWHEN is not going to get results of the first REC statement.
Regards,
Gersh
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Gerch,
Thanks for your reply
My script runs in DEFAULT. I'm using it for calculating other KPIs but in this case I tried the last formula given by Vadim and it worked.
For your suggestion, using [2014.TOTAL] will work only for one year (2014 for example), while I need to do the calculation for all TIME members.
Regards
Maha
Hi Maha,
2014 was just an example to give you an idea of syntax. It can be a FOR/NEXT loop with a variable, like [%YR%.TOTAL] depending on how you get the year value or something else.
I'm glad that Member Formula worked for you, but you probably know that it affects performance.
So, if your requirements allow for storing those ratios then script would be a more efficient way to do it, but in most cases (as Vadim pointed out) it can lead to wrong aggregations.
Regards,
Gersh
Hi Gersh,
FOR/NEXT loop is also not perfect in terms of performance. If for some unknown reason I decided to calculate division of moth value by year total in script logic, then:
1. Create the new property in TIME dimension like YEARMEMBER and fill it with respective YYYY.TOTAL for all months.
2. Use the following script:
*LOOKUP SameCube
*DIM KPI1YEAR:TIME=TIME.YEARMEMBER
*ENDLOOKUP
*XDIM_MEMBERSET KPI=KPI1
*WHEN KPI
*IS *
*REC(EXPRESSION=%VALUE%/LOOKUP(KPI1YEAR),KPI=KPI3)
*ENDWHEN
But in this case I think that dimension member formula is the right solution.
B.R. Vadim
User | Count |
---|---|
6 | |
5 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.