topic Re: Hello Everyone, I am trying to do some calculation in which getting problem. in QlikView

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

Fri, 15 Nov 2013 06:37:16 GMT

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.

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

Fri, 15 Nov 2013 07:15:15 GMT

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

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

kangaroomac — Fri, 15 Nov 2013 07:17:51 GMT

Hiya,

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

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

Fri, 15 Nov 2013 07:29:00 GMT

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

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

Fri, 15 Nov 2013 07:55:36 GMT

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

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

Fri, 15 Nov 2013 08:45:06 GMT

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

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

Fri, 15 Nov 2013 09:30:52 GMT

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

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

Fri, 15 Nov 2013 10:17:38 GMT

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

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

Fri, 15 Nov 2013 13:55:25 GMT

Hi Nupur,

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

I hope this will help you.

Regards,

Ashutosh