Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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.

8 Replies
Not applicable
Author

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

Hiya,

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

Hi Nupur,

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

I hope this will help you.

Regards,

Ashutosh