Database contains: FromDate, ToDate: Show correct values for month in between?

[#1]

Hello everyone,

I need help showing the correct data from database.

The structure of the database is as following:

ID     FromDate     ToDate               Costs

1     10.02.2014     30.06.2014         500€

The problem is:

How can I show the correct data when I want to see the cumulated values for May in example 1?

As QV does not recognize there are any data in there. The correct costs for that month should be 500€ / 140 * 31 = 110,71€.

Thanks in advance!

[#2]

Thank you a lot! Saved my day!

I am relatively new to QV, can you explain to me the Final table?

Why is it separated into two Load´s? Is it possible to put it into one table?

In the last line we use resident to load initial again...Why do we load initial and not final as there are the costs?

[anbu1984]

Outer Load in Final table is called Preceding Load. Preceding Load is used in this case, so we don't need to do calculate MthStrt and MthEnd multiple times in Cumulated_Costs calculation.

Check this link for more details on Preceding Load

http://community.qlik.com/blogs/qlikviewdesignblog/2013/03/04/preceding-load

To create one table, use Join like this or create Precedent load on Inline Load

1. Using Join

Initial:

Load ID,

..

2,25.05.2014,28.05.2014,22 ];

Join(Initial)

Load

  YearMth,

....

Resident Initial While IterNo() <= $(MonthDiff(FromDate,ToDate));

Or

2. Precedent Load on Inline table

Initial:

Load

  FromDate,

  ToDate,

  YearMth,

  If(MthDiff = 1,

  Costs,

  If(FromDate >= MthStrt And FromDate <= MthEnd,

  Costs * Interval(MthEnd-FromDate,'D'),

  If(ToDate >= MthStrt And ToDate <= MthEnd, Costs * Interval(ToDate-MthStrt,'D'),

  Costs * Interval(MthEnd-MthStrt,'D')))) As Cumulated_Costs,

  ID;

Load

  FromDate,

  ToDate,

  Date(Addmonths(FromDate,IterNo()-1),'YYYYMM') As YearMth,

  (Costs/If($(MonthDiff(FromDate,ToDate)) = 1,

  1,

  Interval(ToDate-FromDate,'D'))) As Costs,

  MonthStart(Addmonths(FromDate,IterNo()-1)) As MthStrt,

  MonthEnd(Addmonths(FromDate,IterNo()-1)) As MthEnd,

  $(MonthDiff(FromDate,ToDate)) As MthDiff,

  ID

While IterNo() <= $(MonthDiff(FromDate,ToDate));

Load ID,

Date#(FromDate,'DD.MM.YYYY') As FromDate,

Date#(ToDate,'DD.MM.YYYY') As ToDate,

Costs Inline [

ID,FromDate,ToDate,Costs

1,10.02.2014,30.06.2014,500

2,25.05.2014,28.05.2014,22 ];

Initial table is the source for Final table. Resident load is used to create Final table.