topic Re: how to solve this logic in QlikView

how to solve this logic

Anonymous — Wed, 09 Mar 2016 04:26:29 GMT

Hi All,

I have two tables, table 1 contains multiple products with sales and sales date, Table 2 has product and lead time for each product.

now I want to create a table: where I want to start a start date from calendar object and the sales should be calculated as from StartDate to StartDate+LeadTime.

Suppose for Product A the leadtime is 10 and for Product B the lead time is 6

and If I enter the startdate as 1/20/2016 so the sales for product A should be from 1/21/2016 to 1/30/2016 and for product B from 1/21/2016 to 1/26/2016.

I have attached the sample qlikview file and xl data file.

Below is the expected output for the attached data.

Product	Sales
Product A	100
Product B	60

Thanks for you help.

Re: how to solve this logic

tresB — Wed, 09 Mar 2016 04:42:47 GMT

In this case, set analysis would not be ideal. Try like:

Sum(If(Date>Date(vStartDate) and Date<=Date(vStartDate+LeadTime), Sales))

Re: how to solve this logic

amayuresh — Wed, 09 Mar 2016 06:54:51 GMT

I tried this but is not working for me

Variable vStartDate=Max(SalesDate) then write expression in chart with Product as Dimension

=Sum(If(SalesDate>Date(vStartDate) and SalesDate<=Date(vStartDate+LeadTime), Sales))