sum in script

[#1]

Hi All,

i have data like the following:

ID	Category	Start Date	End Date	Amount
A	1	7-May-2007	31-May-2007	$100
A	1	1-Jun-2007	6-Jun-2007	$85
A	1	7-Jun-2007	6-May-2008	$50
A	1	7-May-2008	6-May-2009	$70
A	3	7-May-2007	31-May-2007	$20
A	3	1-Jun-2007	6-Jun-2007	$12
A	3	7-Jun-2007	6-May-2009	$15
A	4	1-Jun-2007	6-Jun-2007	$5
A	4	7-Jun-2007	6-May-2009	$5

i need to achieve the following result:

ID	Start Date	End Date	Amount
A	7-May-2007	31-May-2007	$100 + $20= $120
A	1-Jun-2007	6-Jun-2007	$85 + $12 + $5= $102
A	7-Jun-2007	6-May-2008	$50 + $15 + $5 = $70
A	7-May-2008	6-May-2009	$70 + $15 + $5 = $90

As the start and end date are not entirely similar among the category, i can't directly do a group by.

The way i thought of is by looping it by day but it'll be slow.

Any idea is highly appreciated. Thanks a lot!

[Anonymous]

There are many ways to skin the metaphorical cat depending on what result you want. Here's one that should work:

T1:
LOAD ID,
Category,
[Start Date],
[End Date],
[Start Date] & [End Date] as DateDiff,
Amount
FROM
BaseData.xls (biff, embedded labels, table is Sheet1$);

join
Load
ID,
DateDiff,
sum(Amount) as SumOfAmount
resident T1
group by ID, DateDiff;

[#3]

Hi Johannes,

Thanks for the swift reply. However, I'm not able to get the result using your script.

Start date and end date are not same for all category, grouping them will not sum as per the period.

Mind attaching your qvw file? Thanks !

[mongolu]

Why don't you group only by StartDate?

[johnw]

I frankly don't understand what your groupings ARE. On first glance, you appear to be grouping by ID, Start Date and End Date. But you aren't.

The first line makes sense. Both the $100 and $20 entry have that start and end date, and no other entries do.

The second line makes sense. The $85, $12 and first $5 entry have that start and end date, and no other entries do.

The third line confuses me. Only the $50 entry has the given start and end date. The $15 and one of the $5 entries starts on the same day, but ends a year after the given end date. So why are they on that line? Because they have the same start date? Because they have the same start date AND end exactly one year after the end date? Something else?

The fourth line also confuses me. Only the $70 entry has the given start and end date. The $15 entry is REPEATED on this line. It does have the same end date, but a different start date. And we also have a $5 entry that ends on that date, even though it starts on a different date.

So on second viewing, it appears that if EITHER date matches, we include the amount. But that's not it either, because that would produce THIS chart:

Start Date End Date Amount
7-May-2007 31-May-2007 $100 + $20 = $120
1-Jun-2007 6-Jun-2007 $85 + $12 + $5 = $102
7-Jun-2007 6-May-2008 $50 + $15 + $5 = $70
7-May-2008 6-May-2009 $70 + $15 + $5 = $90
7-Jun-2007 6-May-2009 $70 + $50 + $15 + $5 = $140

So for some reason, you're excluding the last line of that chart. Do you only use the start date and the end date from the category 1 entries? That's one difference between the last line and the others.

Anyway, either I'm missing something totally obvious that everyone else is seeing, or you seriously need to explain how you're producing your chart, because I'm just not seeing it. Unless the rule really IS "I want to see only the category 1 start and end dates, and then I want to sum up the amounts for anything in any category that has either the same start date OR the same end date." And if that's the rule, it certainly wasn't obvious.

[#6]

Hi John,

Thanks for taking your time interpretting my question. So sorry that my question wasn't clear enough.

Hope i'll do a better job this time.

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

Tenant	Category	Category Desc	Start Date	End Date	Amount
GAP	1	Base Rent	7-May-2007	31-May-2007	$100
GAP	1	Base Rent	1-Jun-2007	6-Jun-2007	$85
GAP	1	Base Rent	7-Jun-2007	6-May-2008	$50
GAP	1	Base Rent	7-May-2008	6-May-2009	$70
GAP	3	Service Chg	7-May-2007	31-May-2007	$20
GAP	3	Service Chg	1-Jun-2007	6-Jun-2007	$12
GAP	3	Service Chg	7-Jun-2007	6-May-2009	$15
GAP	4	Misc Chg	1-Jun-2007	6-Jun-2007	$5
GAP	4	Misc Chg	7-Jun-2007	6-May-2009	$5

My purpose is to calculate staggered gross rent i.e. Base Rent + Service Chg + Misc Chg. Staggered which means if there is a change in the rental during the period. I will also need the date information for e.g. as below:

Tenant GAP will pay rental of

$120 from 7-May-2007 until 31-May-2007;

$102 from 1-Jun-2007 until 6-Jun-2007;

$70 from 7-Jun-2007 until 6-May-2008;

$90 from 7-May-2008 until 6-May-2009

Hope this explains it.

[johnw]

OK, so from 7-May-2007 to 31-May-2007, we charge the base rent of $100 plus the service charge of $20.

From 1-Jun-2007 to 6-Jun-2007, we charge the base rent of $85, plus a service charge of $12, plus a misc charge of $5.

From 7-Jun-2007 through 6-May-2008, we charge the base rent of $50. For the service charge of $15 from 7-Jun-2007 through 6-May-2009, this means that we should charge $15 for every base rent period within the service charge period? So the 7-Jun-2007 through 6-May-2008 period gets charged a full $15? Same rule for the $5 misc charge fro 7-Jun-2007 through 6-May-2009? So we charge $50 + $15 + $5 for $70 total.

For 7-May-2008 through 6-May-2009, we charge the base rent of $70. Following the rule above, we also charge the $15 service charge and the $5 misc charge AGAIN based on their 7-Jun-2007 through 6-May-2009 periods, for a total of $90.

So is that the rule? Everything is based on the Base Rent periods, all other categories of charges will have periods that are ALWAYS composed of one or more COMPLETE AND CONTIGUOUS Base Rent periods, and the amount shown should be charged to ALL of the Base Rent periods inside of the other category's period?

[#8]

Yes, John. Spot on.

[johnw]

OK! Now to actually work on a solution. I'll try to get to this soon. Does the answer need to be a script solution, or is a chart expression that does the same thing (if that ends up being easier) OK?

(Edit: I haven't forgotten this. Just didn't get to work on it today other than some thoughts on how I want the data model to look. Specifically, that I want to break the other categories apart by the base category's date ranges so that the expression can then be a simple sum. I just need to work out the best way to do that. Or maybe I'll have some other thought on how to approach it.)

[johnw]

Well, I followed my thought yesterday to break the other categories apart by the base rental periods, and then just sum(Amount). It just turned out to be harder and more complicated than I was expecting. Maybe there's a better way, but see attached for one way.