Calculating sum in a loop

[Daan_L]

Hello,

Perhaps somebody can help me with a problem I'm having. I have the following QVD:

Test:

LOAD

CompanyId,

DateId,

StartingDate,

EndDate,

Revenue

The EndDate field value is always the same as the DateId (I use DateId for a different left keep, so I want to keep it that way). StartingDate is a calculated value and depending on DateId always a year plus 1 day back. So if DateId is 1-1-2024, StartingDate value will be 2-1-2023. For each DateId I have a calculated revenue (it is a number field). I want to know the total sum of all the revenue values between the given starting and enddate. So if my value on StartingDate is 2-1-2023, my DateId and EndDate will be 1-1-2024. In the loop it needs to calculate the sum of all the revenue values between the StartingDate and the EndDate. If the starting date is 3-1-2023 and the end date is 2-1-2024, I want the total sum of all the revenue values that fall between those dates. When I have these totals, I can then continue with my script and determine a total value for revenue in the past 12 months for each DateId.

What I would like to do is the following:

LET loopDate = MAKEDATE(2019,1,1);

DO WHILE loopDate <= MAKEDATE(TODAY())

LOAD
CompanyId,
SUM(Revenue) AS Total
RESIDENT Test
WHERE StartingDate >= NUM($(loopDate)) AND EndDate <= NUM(TODAY())
GROUP BY CompanyId;

LET loopDate = loopDate + 1;

LOOP

This loop isn't working however. Can you please help me with this? 

If you need any additional information, please let me know.

[LRuCelver]

I don't fully understand what you are trying to do, but I believe this line is your problem:

DO WHILE loopDate <= MAKEDATE(TODAY())

Today() already returns a date. If you are lucky this is whats causing you loop to fail. Otherwise MakeDate interprets the today's date as an integer and uses it as the year, which will make your loop run past year 45000.

I'm fairly certain that you don't even need a loop for whatever you are trying to do. Maybe we can help you better if you could share a small example of the input data and what the result should be.

[Daan_L]

Hi,

Thanks for the reply, perhaps I can make it easier with the file I included? In the file I have given some realtime examples of the data I'm working with. DateId is a self written daynumber, the values in column B result in de dates given in column F. The daynumber given in column C results in the dates given in column G.

If I make the script easier it will look like this:

LOAD
CompanyId,
SUM(Revenue) AS Total
RESIDENT Test
WHERE StartingDate >= 45292 AND EndDate <= 45664
GROUP BY CompanyId;

The value that is given in field Total will then be 198.293,60. 

What I would like to do is calculate the field Total for each DatumId. When I select 1-1-2024 (or 45292) this total will show 9871,02, when I select 2-1-2024, it will also show 9871,02. When I select 4-1-2024, it will give the sum of all the revenue given above, so 10.171,02. When I select the last DateId it will show 198.293,60.

According to you message, I don't even need the loop? How could I do this easier?

[LRuCelver]

Thanks for the example!

Is this what the final result should look like?

I've used IntervalMatch() to assign the DateIds to all matching intervals defined by the StartingDate and EndDate.

Here is the full script:

//	Loading Data
Data:
Load
    CompanyId,
    Date(DateId) as DateId,
    Date(StartingDate) as StartingDate,
    Date(EndDate) as EndDate,
    Revenue
From [lib://AttachedFiles/Example.xlsx]
(ooxml, embedded labels, table is Blad1);


//	Preparing a temp table to join all revenue values to all matching intervals defined by start and end dates.
Temp:
NoConcatenate Load
	CompanyId,
    DateId as TotalDateId,
    Revenue as TotalRevenue
Resident Data;

//	Joining the interval keys
Join(Temp) IntervalMatch(TotalDateId, CompanyId) Load Distinct
	StartingDate,
    EndDate,
    CompanyId
Resident Data;

//	Joining the remaining fields to the interval
Join(Temp) Load Distinct
	StartingDate,
    EndDate,
    CompanyId,
    DateId,
    Revenue
Resident Data;

Drop Table Data;


//	Calculating the total
Final:
NoConcatenate Load
	CompanyId,
    DateId,
	StartingDate,
    EndDate,
    Revenue,
    Sum(TotalRevenue) as Total
Resident Temp
Group By
	CompanyId,
    DateId,
	StartingDate,
    EndDate,
    Revenue;

Drop Table Temp;