topic Opening and Closing Calculation in QlikView

Opening and Closing Calculation

lalitinmbd — Mon, 07 Nov 2011 05:54:53 GMT

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.

Opening and Closing Calculation

Anonymous — Mon, 07 Nov 2011 14:00:01 GMT

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.

Opening and Closing Calculation

lalitinmbd — Tue, 08 Nov 2011 06:00:07 GMT

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)

Re: Opening and Closing Calculation

Sokkorn — Tue, 08 Nov 2011 06:42:01 GMT

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

Opening and Closing Calculation

lalitinmbd — Tue, 08 Nov 2011 07:13:34 GMT

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.

Opening and Closing Calculation

Sokkorn — Tue, 08 Nov 2011 07:25:14 GMT

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

Opening and Closing Calculation

lalitinmbd — Tue, 08 Nov 2011 07:34:07 GMT

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.

Opening and Closing Calculation

Anonymous — Tue, 08 Nov 2011 11:10:47 GMT

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

Opening and Closing Calculation

lalitinmbd — Thu, 10 Nov 2011 11:10:43 GMT

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...

Opening and Closing Calculation

lalitinmbd — Tue, 15 Nov 2011 07:00:53 GMT

Thanks alot..

Its working....

Re: Opening and Closing Calculation

vikasmahajan — Thu, 22 Jan 2015 05:22:52 GMT

Can u give solution for my thread Cummulative Graph

Vikas