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

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

Mon, 24 Nov 2014 10:25:10 GMT

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!

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

anbu1984 — Mon, 24 Nov 2014 11:32:24 GMT

Initial:

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 ];

Final:

Load 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'))));

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

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

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

Mon, 24 Nov 2014 12:09:50 GMT

Thank you a lot for your work!

Where do I have to put these formulas? (Especially what does Initial and Final mean?)

Putting these data into the script gives me an error that there is a missing ')' in the 'Final' code.

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

anbu1984 — Mon, 24 Nov 2014 12:34:00 GMT

Initial - Input table

Final - Final table

I missed following in previous post

SET MonthDiff = Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1))) + 1);

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

Mon, 24 Nov 2014 14:54:34 GMT

Thank you very much for clarification

It works. There is a minor mistake in it: it generates a YearMth field vor every entry although the entry may not even contain that YearMth.

Example: 201406 is being created for ID=2.

Therefore there are false values for all entries (see attached file).

How to fix that?

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

anbu1984 — Tue, 25 Nov 2014 06:15:42 GMT

201406 in this pic is for ID=1. Last row 201405 is for ID=2.

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

Tue, 25 Nov 2014 09:02:20 GMT

The problem is that QV is making a cross table as soon as I add the ID to any sort of table (which gives false values).

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

anbu1984 — Tue, 25 Nov 2014 09:10:58 GMT

Can you post sample app with your issue.

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

Tue, 25 Nov 2014 09:17:06 GMT

Yes.

Thanks for your patience.

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

anbu1984 — Tue, 25 Nov 2014 09:48:31 GMT

Added ID to Final table. Check this app

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

Tue, 25 Nov 2014 15:32:27 GMT

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?

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

anbu1984 — Wed, 26 Nov 2014 05:55:31 GMT

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));

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,

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,

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.