Opening and Closing Calculation

[lalitinmbd]

Dear All,

I am presently working on the opening and closing logic for attrition calcuation and i am stuck.

i have implemented the logic in the attached file. the logic is working fine for month on month

but when i put any dimension in the chart and select multiple months then it gives me the wrong calculation

for attrition.

Attrition formula = (No of resignies in the Selected month)/((Closing for MaxMonth+Opening for Minimum Month)/2)

Please refer the attach qvw file for detail.

[Anonymous]

Hi,

in my opinion, the problem is that for the "General Manager" in flag "RE", there is no data for march available.

You donot need to add the field "YEARMONTH" in set analysis. Try the following statement.

=Count({<Flag={'RE'}>}DISTINCT CommonID)/((Max(Closing)+Min(Opening))/2

I hope that this works for you.

[lalitinmbd]

Thanx for your reply.

I tried the solution given by you.

But this is not working in the scenerio mentioned below.

Year     Month      Opening       Closing          Resignies

2011        4             419             415                 10

2011        5             415             417                   4

2011        6             416             416                   4

By using the Formula

=Count({<Flag={'RE'}>}DISTINCT CommonID)/((Max(Closing)+Min(Opening))/2

it will take Min(Opening)=415

                Max(Opening)=417

which will compute the wrong value.

Correct case is written below

                Min(Opening)=419

               Max(Closing)=416

               resignies=18

so that correct value will be=18/((419+416)/2)

[Sokkorn]

Hi,

I'm still blur with your description

Year     Month      Opening       Closing          Resignies
2011        4             419             415                 10
2011        5             415             417                   4
2011        6             416             416                   4
By using the Formula
=Count({<Flag={'RE'}>}DISTINCT CommonID)/((Max(Closing)+Min(Opening))/2
it will take Min(Opening)=415          ==> Correct
                Max(Opening)=417        ==> Should be 419
which will compute the wrong value.
Correct case is written below
                Min(Opening)=419
               Max(Closing)=416
               resignies=18
so that correct value will be=18/((419+416)/2)  ==> With your formular should be = 18/((417+415)/2)

By the way, I have attached with your formular and noted that it work fine.

Regards,

Sokkorn

[lalitinmbd]

lets say if i select year=2011 and Q1

the formula to calculate Attrition is

=(No. of Resiginies in Q1 2011)/

((Closing of Max Month(i.e Closing of Jan)+Opening of Minimum Month(i.e Opening of Mar))/2)

In case of General Manager  for same selection there is no data in Month of March 2011 it is taking Closing as 0 which is giving the wrong value. In this it sohuld take closing of Feb.

[Sokkorn]

Hi,

If you use:

=(No. of Resiginies in Q1 2011)/

((Closing of Max Month(i.e Closing of Jan)+Opening of Minimum Month(i.e Opening of Mar))/2)

Problem maybe occure when user select month.

Just an idea from me is create two variable for MAX(Month) and MIN(Month) then use both of them in set analysis.

Regards,

Sokkorn

[lalitinmbd]

If the user selects 1 month at a time then the formula is working correctly.

but in case of multiple selection in month if data is not there in max month or min month against dimension(i.e Designation) then it takes 0 instead of picking the value from previous month.

I need to check that if against dimension there is no data in max month it should take the closing from previous month and in case there is no data in previous month it should go to previous to previous month.

same in case of opening.

[Anonymous · Accepted solution]

Hi,

I would suggest to create two variables. But not only Max(Month) and Min(Month) but

vMaxClosing = Sum({YearMonth={$(=max(YearMonth))}>}Closing) and also vMinOpening

and then use the variables in your expression

Count({<Flag={'RE'}>}DISTINCT CommonID)/((vMaxClosing+vMinOpening)/2)

That might help also for your Q2 data.

Regards

[lalitinmbd]

Hiiiiiiiii,

I tried the solution by taking the

Sum({YearMonth={$(=max(YearMonth))}>}Closing) values into the variables

but its also not giving the desired result...

[lalitinmbd]

Thanks alot..

Its working....