Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Problem

Hello all,

I'm having a small issue which I don't know how to solve. I have a table containing our sold Machines and one table with our invoices. I created one fact out of these two. Now I need to calculate the redemption of my machines over time. The table simplified looks like below:

Load *, recno() as ID INLINE [

Start, End, SerialNo, PriceSold

15/01/2011, 15/01/2016, AAAA, 18000

08/08/2011, 08/08/2016, BBBB, 36000

03/04/2012, 03/04/2017, CCCC, 72000

.......

];

Every machine has a 60month redemption period (PriceSold/60 will give me how much the machines redemption is each month).

I want to achieve the total redeption of each machine on a yearly base.

Year 2011: 6600€ (Machine AAAA: 3600, Machine BBBB: 3000)

Year 2012: 21600€(Machine AAAA: 3600, Machine BBBB: 7200, Machine CCCC: 10800€)

Year 2013: 25200€(Machine AAAA: 3600, Machine BBBB: 7200, Machine CCCC: 14400€)

Year 2014: 25200€

Year 2015: 25200€

Year 2016: 19500€(Machine AAAA: 300, Machine BBBB: 4800, Machine CCCC: 14400€)

Year 2017: 3600€ (only machine CCCC is still in this period)

I already created a temp calendar which generated all possible dates in between:

Let vMinDate = num(Date(15/01/2011))

Let vMaxDate = num(Date(03/04/2017))

Calendar:

Load Date($(vMinDate)+RecNo()-1) as Date Autogenerate $(vMaxDate) - $(vMinDate) +1;

FullTable:

Join (Data) IntervalMatch (Date) Load

Start,End

Resident Data;

The above actions don't give me the result I want. Could someone help me out with this one?

If you need more information, or something isn't clear, just ask me.

Thanks in advance all

--- Edit:

I found the solution to my problem. I just needed to do a distinct on the year of the startdate when joining.

1 Reply
Not applicable

Re: Problem

I found the mistake I made. When creating my FullTable I only needed my years.

FullTable:

Join (Data) IntervalMatch (Date) Load

distinct(year(Start)),End

Resident Data;

After the reload I was able to calculate my totals the way I wanted.

Community Browser