Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

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!

Tags (2)
1 Solution

Accepted Solutions
anbu1984
Honored Contributor III

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

Added ID to Final table. Check this app

11 Replies
anbu1984
Honored Contributor III

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

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

Not applicable

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

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.

anbu1984
Honored Contributor III

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

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

Not applicable

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

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?

anbu1984
Honored Contributor III

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

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

143279.png

Not applicable

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

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

anbu1984
Honored Contributor III

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

Can you post sample app with your issue.

Not applicable

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

Yes.

Thanks for your patience.

anbu1984
Honored Contributor III

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

Added ID to Final table. Check this app

Community Browser