Hello Everyone, I am trying to do some calculation in which getting problem.

[#1]

Hello All,

I am calculating DaysOfSupply the condition of which is like:

Table:

Product          Location       DateOfForecast         ForecastStock          ProjectedStock         DaysOfSupply

A                       1                   1/jan/2013                 200                             100

A                       1                   2/jan/2013                 300                             300

A                       2                   1/jan/2013                 400                             200

A                       2                   2/jan/2013                 100                             50

B                       1                   1/jan/2013                 200                            100

B                       1                   2/jan/2013                 300                             300

B                       2                   1/jan/2013                 250                             350

B                       2                   2/jan/2013                 100                             300

Now DaysOfSupply has calculation like:

if(ProjectedStock>next(ForecastStock),ProjectedStock=ProjectedStock-ForecastStock and DOS=DOS+1,DOS=DOS+(ProjectedStock/ForecastStock))

then the Chart will Contain DOS Final value in DateOfSupply Column.

This is a kind of function in C as:

DOS=0 initially for each Product and each location and for each date,

functionA()

{

     if(ProjectedStock>next(ForecastStock))

     {

          ProjectedStock=ProjectedStock-ForecastStock;

           DOS=DOS+1;

          functionA();

     }

     else

          {    

               DOS=DOS+(ProjectedStock/ForecastStock);

          }

}

how should i do it in qlikview ,i don't know.

Thanks and Regards.

[#2]

Hi,

     Can you please mention in your example that what output should come in DateofSupply field. So it will be more easy to understand your problem.

Thanks,

Ashutosh

[kangaroomac]

Hiya,

Not too sure if this is what you're after, but hope it helps.

[#4]

Hi,

     Actually I want to know that you are taking DOS = 0 on the basis of Distinct Product and Location field or only at initial time?

Thanks,

Ashutosh

[#5]

Hi,

    Check this file. I update liitle bit your file and please let me know if this will not according to your requirement.

I hope this will help you.

Thanks,

Ashutosh

[#6]

Hii Ashutosh,

Thanks for Some helpful reply,

But my requirement is something different,

As DOS is a kind of variable which i am trying to calculate daysofsupply for each date and each product.

Thats why i have taken its initial value as 0.

as per your file that requirement is not fulfilled.

I need my data like this:

Table:

Product          Location       DateOfForecast         ForecastStock          ProjectedStock         DaysOfSupply

A                       1                   1/jan/2013                 200                             100                    if(100<300(this is next                                                                                                                                   date forecast),100/300)

A                       1                   2/jan/2013                 300                             300                    - as no next forecast is                                                                                                                                                  avaialable for it

A                       2                   1/jan/2013                 400                             200                    if(200>100,a flag                                                                                                                                                  increased its                                              value by 1 and now variance between these two values are used for next comparision untill we get variance less than next forecast,if so come then add ProjectedStock/ForecastStock in flags value)

A                       2                   2/jan/2013                 100                             50                    - as no next forecast                                                                                                                                                  is avaialable for it         

B                       1                   1/jan/2013                 200                            100

B                       1                   2/jan/2013                 300                             300

B                       2                   1/jan/2013                 250                             350

B                       2                   2/jan/2013                 100                             300

[#7]

Hi,

     Let me clear two things:

1.  Are you try to compare current ProjectStock value with next ForeCastStock value (which is available in next row).. Am I right?

2. As you mention for second row in DatOfSupply field "no next forecast is available for it". So there what you want to show in DateOfSupply field. I know you are increasing DOS variable value by 1 at that time.

Thanks,

Ashutosh

[#8]

Yes Right Ashutosh,

Means for Last Product related to Location will not contain any value in date of supply as we donot have any next forecast for the same.

and again the condition is somewhat like.

If I have 200 as ProjectedStock today then i need to find for next how many days i can use it by comparing it with next days forecast and if my present ProjectedStock>Forecast then it increase the value of DOS by 1 and now in same condition ProjectedStock will be replaced by ProjectedStock-Forecast and then go with same  calculation again, at last if i get the ProjectedStock<Forecast i will add DOS value with ProjectedStock/below(Forecast).

tell me if u got the requirement or not.

Thanks and Regards,.

[#9]

Hi Nupur,

    Check this attach file, I also mentioned comments in this  file.

I hope this will help you.

Regards,

Ashutosh