Hi All,
This week, an interesting question was posted by madridnorrland hereUsing of variables and input controls in web intelligence rich client .
From which I could understood, he wants to create a whole new object based on an entry from a Input Control.
This can be easiest achieved by creating it directly on the Universe, but what if he doesn't have access to the Universe or the IT people are too busy ?
I will use the e-fashion universe as an example. Lets suppose you want to create a montlhy target (in thousand US$) value for sales revenue , these monthly values are the same for all stores in 2006 .
This is my query
These are my monthly target values :
These targets are entered as a single string in which individual values are separated by ";" and the string itself ends with a ";" . The string is stored in a measure variable [target] ="100;200;50;100;120;80;100;150;120;110;200;300;"
The idea here is to calculate [remainig], a measure variable, that ,for month n+1 get the value for month n and took off its target value.
So , for month 2, the value of remaining would be :
200;50;100;120;80;100;150;120;110;200;300;
which is the value for month 1
100;200;50;100;120;80;100;150;120;110;200;300; when I took off 100;
With [remainig], the [Montlhy target] can be easily calculated using [Montlhy target] = Substr([remaining],1,Pos([remaining];";")-1)
The table below shows, for each month the values of [Monthly Target] and [remainig].
Table 1
The challenge here is to calculate the value of remainig for each month, which is , in fact, a recursive calculation. As far as I know, the only way to use recursiveness in WEBI is through the use of Previous(Self) function.
The formula for [remainig] is= If(IsNull(Previous(Self));[target];Substr(""+Previous(Self);Pos(""+Previous(Self);";")+1;Length(""+Previous(Self))))
Which basically does the following :
1 - For the first rmonth, its value would be [target]
2 - For the second on it will take the value of the preceding month and apply the Substr function from the position of the first ";" to the end of the strring.
As an example, the second month is calculated as :
If(IsNull(Previous(Self));[target];Substr("100;200;50;100;120;80;100;150;120;110;200;300;";Pos("100;200;50;100;120;80;100;150;120;110;200;300;";";")+1;Length("100;200;50;100;120;80;100;150;120;110;200;300;")))
The reason for using ""+Previous(Self) as the argument for the functions is that WEBI doesn't know the type of Previous(Self) so I forced to considered it as text using ""+.
Okay, now I got [Monthly Target] calculated in the context of Month, if I add another dimension to Table 1 , [Store Name] here's how it looks like now.
Table 2
It happens because, in fact, the values of remainig are calculated not in context of [Month] but in the context of the block in which it's placed.
To calculate [Montlhy Target] exclusively on the context of [Month], I create a new measure variable [target_month] =
ToNumber([Montlhy Target];"000") ForAll([Year];[Store name]) ForEach ([Month]).
The ToNumber() function is used to transform the text variable [Montlhy Target] into number.
The forAll is used to exclude from the calculation context all dimensions but month and the forEach is used to individualize the value of [target_month] for each [Month Value], so if I add [target_month] to table 2.
Table 3
As you can see , the column in red , target_month, assigns, for each month , the correct value, differently from Monthly Target (in blue).
One more reminder is that the calculation is based on the natural sort of [Month], so if you alter the order of [Month], the values will change (see table below)
Table 4
The .wid file of this blog is available, for free, to download at
http://rogerplank1234.weebly.com/ with the name of montlhy_target.wid and was built with WEBI 4.1 SP5
The final result can be seen it the below picture in which I set the value of color revenue green if [sales revenue] greater than or equal to
[thousand month target] and red otherwise.
Any comments will be welcome
Regards,
Rogerio
- SAP Managed Tags:
