Cumul inverse

[#1]

Hi,

I recently finished the Developer-Course and am now trying to develop a view showing the development of the total sum of a specific kind of turnover. These numbers are available in an excelfile (originating from a DB2-file that I will want to use eventually) containing following data:

Company, MemberNo, YearWeekNo, Turnover, etc.

In the scipt, I combined Company & MemberNo to a unique keyfield 'MemberID' and interpreted the YearWeekNo to be able to separate the 'Year'- and the 'Week'-value. No problem sofar.

But the turnover is only available to me as a cumulative figure, i.e.:

MemberID	Year	WeekNo	Turnover
001-1	2007	52	1.250.000
001-2	2007	52	875.000
001-3	2007	52	1.000.000
…
001-1	2008	1	100.000
001-2	2008	1	50.000
001-3	2008	1	75.000
…
001-1	2008	2	140.000
001-2	2008	2	110.000
001-3	2008	2	65.000
…

Based on this data, I would like to report the weekly changes in the turnover. For MemberID 001-1 and the year 2008, this means that I need the values 100.000 for WeekNo 1 and (140.000-100.000) for WeekNo 2.

Ofcourse, I must be careful not to deduct the sum of the turnover in 2007, WeekNo 52 from the 100.000-value established in WeekNo 1 of the next year. And I should make sure that the data is ordered by MemberID (asc), Year (asc), WeekNo (asc).

Turnover can also be negative, by the way, as is the case with MemberID 001-3 in this example.

This means that I do not think I can use an 'Above'-statement - or can I, and how should I do that? Or what alternatives are there, apart from calculating the weekly changes in the Excel-sheet prior to loading it into QlikView?

[#2]

Hello Guido,

Guido Appels wrote:This means that I do not think I can use an 'Above'-statement - or can I, and how should I do that?

--> I would take care using above() and similar functions because of the flexibility QV gives to users. Of course I use this type of function within a rangesum() and so on. But think about different chart(-type)s and listboxes with the ability of selecting, filtering and sorting nearly everything. And suddenly above isn't above as before. So I would try to calc these deltas (differences) during loading (e.g. there you can be sure the order is as you expect it) in an additional field. Perhaps an additional field with a week-serial regardless of the year may be helpful. OK?!

Regards, Roland

[#3]

Hello Roland,

Thank you for your reply. An extra week-serial might work, I will consider it. However, I am now trying to make it work with a for..next-statement. Wish me luck!

Guido

[#4 · Accepted solution]

Made it work, not using a for..next-statement but an if..then..else-statement. It took three steps:

1. Loaded the original Excelfile (a smaller copy, to speed up the process);

2. Then loaded a copy from the resident Excelfile, ordering it by MemberID ASC, Year ASC, WeekNo ASC; dropped the original Excelfile -table made in step 1;

3. Then loaded the resident copy made in step 2, this time with an extra statement:

If(MemberID=peek('MemberID') and Year = peek('Year'),

[Turnover] - peek([Turnover]),

[Turnover]) as [Turnover specific week]

Then I dropped the table made in step 2.

Seems to work like a charm. Now on to the next challenge! Thanks again tot Roland for his advice; I hope this is a valid solution, since I solved this in the loading process, not front-end where users could get strange results sorting the data otherwise.

Guido

[#5]

Hello Guido,

this sounds great. Wish you more success with QV and don't hesitate to post again.

RR