If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

average(please urgent )

Started by anilkumar, 08 Nov 2009 07:04:56 AM

Previous topic - Next topic

anilkumar

HI guys

I WANT DO AVERAGE FOR 1 MESURE

i have one measure called  VOLUME

                              MONTHS FROM 1-12 and full year

jan  feb  mar - - - - - - - dec  full year

1000  2000 3000                     2000
           
now  i need average.

i dont have idea which BIF'S I WANT USE

i  tried by using movavg

i din't get correct results

please help me out

Regards
ANIL K .MARRI

Extirpator

Anil,

If you just want a simple average, why not just use the formula {full year}/12?

Extirpator

anilkumar

HI  Extirpator

if i have data for 5 months it has to calculate for

5 months data division by 5 months only
say for exp   jan -may
                    10000   


result should be 10000/5=2000
  6 months    jan-jun  30000
result  30000/6=5000

like this  i need

Regards
ANIL K .MARRI


doyler2000

Hi - this is not a very scientific approach but it works.

Volume - Base Data
Volume lag one = Use lag Bif to lag the base data by one
Volume lag two = Use lag Bif to lag the base data by two
Volume lag three = Use lag Bif to lag the base data by three
Volume lag four = Use lag Bif to lag the base data by four
5pt Average = Sum(All of above)/5

You will probably need to go right down to Volume lag 11, and have lines for 2pt Average right down to 12pt average. I then use a complicated Admin link to display the correct Avg in each month in a seperate cube.

Like i said - not scientific but it works.

N

Extirpator

#4
Anil,

Thank you for clarifying. Are you willing to set a flag for months that have data? If so, then this solution might be simple to implement.

Assume your D-List has at least these two items: Flag and Volume.
In the Calc. Option for Volume, select Weighted Averages and weight by Flag.
The Flag must be 1 if the month has occurred and 0 if it has not.

If you need a total to date, you can add Cumulative_Volume and use the Bif @Ytd(Volume) with Calc. Option set to Time averages = Last period. I believe your months will need to be a timescale if you want to use this Bif.

Extirpator

anilkumar

hi Extirpator

thank you  for spending  your valuable time.

can u please brief out (IF IT IS POSSIBLE STEPS)
bcz iam new to this planning

Regards
ANIL K .MARRI

anilkumar

hi Extirpator


Thank you very much its working fine

Regards
ANIL K.MARRI

anilkumar

hi Extirpator


is there another way of  doing average (VOLUME),
user's not accepting  after entering value in VOLUME  then  they have to
enter 1 in FLAG.


OR
is there any of auomate the enter values in FLAG


Regards
anil k .marri

craig_karr

#8
I'm not sure I understand how the model looks like, but if it looks like I think it looks like then I would do the following:

I would add a new item do the dlist that contains the flag and the volume. I would call that item volume_for_months_occured. I would then make an internal dlink to that item transfering the volumes for all months that has occured. I would then have the following calculation on that new item: IF {volume_for_months_occured} <> 0 THEN 1 ELSE 0

So that the flag is automatically set to 1 if volumes are entered into months that have occured.

Extirpator

I agree with craig_karr. If your users are in Analyst, then they will probably not agree to run the internal D-Link any more than they are willing to enter a 1 in the Flag. However, if your users are in Contributor, the internal D-Link will automatically update and is a perfect solution. Once you have it working, you can even hide the flag to make it completely transparent to the user.

The problem is that you can not perform a weighted average on a calculation; hence the need for the link.

Add Linked_Flag to your D-List.
Changed the Calc. Option for your Volume to be weighted on Linked_Flag.
Change the Flag calculation to IF Volume=0 THEN 0 ELSE 1.
Save D-List.
Create an internal D-Link that moves Flag to Linked_Flag and add it to the D-Cube update. (Go to your cube and then select D-Cube, D-Links, Internal. Right click on the yellow arrow of the dimension that contains your volume and Change to Allocation. Then click on Flag on the left and click on Linked_Flag on the right. Save. Confirm that you do want to add it to the D-Cube update.)

To test it manually, do not enter anything into Flag or Linked_Flag. Just enter volumes and Run Update Links to see how Contributor would behave.

Even if your users are in Analyst (and not using Contributor), perhaps they are willing to push the button to update. They will only enter volumes and push the update icon.

Extirpator