topic sum in script in QlikView

sum in script

Tue, 27 Oct 2009 11:44:58 GMT

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!

sum in script

Anonymous — Tue, 27 Oct 2009 13:56:30 GMT

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;

sum in script

Tue, 27 Oct 2009 16:32:01 GMT

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 !

sum in script

mongolu — Tue, 27 Oct 2009 16:35:03 GMT

Why don't you group only by StartDate?

sum in script

johnw — Wed, 28 Oct 2009 02:52:34 GMT

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.

sum in script

Wed, 28 Oct 2009 08:02:08 GMT

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.

sum in script

johnw — Wed, 28 Oct 2009 22:37:48 GMT

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?

sum in script

Thu, 29 Oct 2009 07:52:29 GMT

Yes, John. Spot on.

sum in script

johnw — Thu, 29 Oct 2009 22:30:53 GMT

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

sum in script

johnw — Sat, 31 Oct 2009 02:52:43 GMT

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.

sum in script

Sat, 31 Oct 2009 03:00:08 GMT

load id , start Date , min ( End date ) , sum ( Amount )

from table

sum in script

johnw — Sat, 31 Oct 2009 03:44:48 GMT


Ivanildo Buarque wrote:load id , start Date , min ( End date ) , sum ( Amount )
from table

You mean this?

[Charges]:
LOAD
"Tenant"
,"Start Date"
,min("End Date") as "End Date"
,sum("Amount") as "Amount"
RESIDENT [Raw Data]
GROUP BY
"Tenant"
,"Start Date"
;

That doesn't work because some of the service and miscellaneous charges are supposed to apply to more than one base rental period. This approach only applies them to the first base rental period.

sum in script

Tue, 03 Nov 2009 14:10:25 GMT

Thanks a lot, John!

i was creating a table for each category and join them together but was having issue with null.

Thanks again!

sum in script

Tue, 03 Nov 2009 15:22:04 GMT

hey John,

i just read your explanation on the rounding function. what would you expect to get after rounding 12345.555 to 2 decimals point in QV?

if my script is :

load round(12345.555, 0.01) as rd autogenerate 1;

i'll get 12345.55

if my script is:

Z:
LOAD * INLINE
[A,B
12345.555,211.3213123];

LOAD
round(A,0.01) as A,
num(B, '#,##0.00') as B,
A as OriA,
B as OriB
Resident Z;

drop table Z;

i'll get 12345.56.

putting =round(12345.555, 0.01) on text object will return me 12345.55 as well.

sum in script

johnw — Wed, 04 Nov 2009 00:33:49 GMT

According to QlikView's documentation, you should get 12345.56, but the documentation doesn't always match reality due to the limitations of binary floating point for representing decimal numbers.

One possible explanation (for which I have almost no evidence) would be if QlikView uses more than one numeric format internally. My understanding was that it was using IEEE double precision binary floating point for all numbers, but Rob Wunderlich's memory usage analysis indicated that shorter numbers took less memory, perhaps indicating a second numeric format. Perhaps the second format is IEEE single precision binary floating point. That allows for 24 binary digits, which in general can only store seven decimal digits, but your eight-digit number can technically squeeze in since it is small. So my theory is that when you use 12345.555 individually in a table or text box, it gets represented with single-precision, but when you combine it in a table with 211.3213123, that has enough digits to require double-precision for the column, resulting internally in a slightly-different approximation, one which rounds up instead of down.

It should be easy to gather some evidence for or against this theory. I need to leave this open in a tab and do that later today.

Edit: Well, that theory's already out the window. I see that you put them in separate columns instead of rows, which should give QlikView the ability to use different formats for each number. I'll need to think of another theory.