Date Total on Report

[spain246]

Ok, I'm trying to do a calculation that tells me the number of days between a date range. For example:

The report is has these dates, 01/01/2005, 02/05/2005, 01/01/2005 02/06/2005 = 3 days (yes I know I repeated a date, but I need that filtered out).

Here is the expression I am looking at. I pulled it out of the alpha help file, but it keeps telling me that it is an invalid expression. I have adjusted it to what I need.

Days=TABLECOUNT("cdr.DBF","Between(offhookdate, "(Cdr-"Offhookdate"= Var-"begindate ) .and. (Cdr-"Offhookdate"=Var-"enddate )")

The variables begindate and enddate are both set as global variables (as D) and they are set from the filter.

Am I trying to get this to do too much here, since the records are based off a filtered set? This is driving me nuts a little here.

Thanks!
Steve Hall

[Stan Mathews]

RE: Date Total on Report

"trying to do a calculation that tells me the number of days" - tablecount() returns a number of records satisfying a filter, no more, no less.

Paste

TABLECOUNT("cdr.DBF","Between(offhookdate, "(Cdr-"Offhookdate"= Var-"begindate ) .and. (Cdr-"Offhookdate"=Var-"enddate )")

into the interactive editor and you should see that, whatever this means, you have an unmatched set of quotes, rendering the expression invalid.

[martinwcole]

RE: Date Total on Report

try this instead:

TABLECOUNT("cdr.DBF","Between(offhookdate, "(Cdr-"Offhookdate"= Var-"begindate ) .and. (Cdr-"Offhookdate"=Var-"enddate )")

tablecount("cdr","between(offhookdate,var-"begindate,var-"var-"enddate)")

[spain246]

RE: Date Total on Report

Well, I did that and I think I fixed the quotes. Here is what i'm getting now.

Days = TABLECOUNT("cdr.dbf", "Between(cdr-"offhookdate{"+ dtoc(Begindate) +"}, {"+ dtoc(VarD-"EndDate) +"})")

It is still telling me that this is an invalid calculation. When I run it through the interactive editor, it tells me "field not recognized."

I am running this report off of a set. I know my variables are carrying across since my 2 "between" dates are showing up on my report. (In the header where I need them to). I have no idea why this is still coming up as invalid. Since the report is already being filtered when it is run, is it possible that I can use the tablecount() to just count the filtered records from the set?

Steve Hall

[Stan Mathews]

RE: Date Total on Report

I'm not at a machine with Alpha at present but the documentation doesn't show the table-" syntax when using tablecount(). Also I don't think "VarD-"" is proper for referring to a variable. Seems it should be "Var-"". Also the structure of between() is BETWEEN( Expression as D, Lower_Value as D, Higher_Value as D ) so I think you are missing a comma after offhookdate. You do know that you can cut and paste between the expression builder/interactive editor in Alpha and this message board?

Try

TABLECOUNT("cdr.dbf", "Between(offhookdate,{"+ dtoc(Var-"Begindate) +"}, {"+ dtoc(Var-"EndDate) +"})")

but I'm working without the syntax hilighting in Alpha so this may just be closer.

[Stan Mathews]

RE: Date Total on Report

I think this is just a variation of what Martin was suggesting. I didn't look at his response closely enough.

[G Gabriel]

RE: Date Total on Report

Steve:
" I have no idea why this is still coming up as invalid"
It is invalid for several reasons:
First: your Between() expression is missing a comma
Second: VarD-"EndDate should be Var-"EndDate
Third: dtoc(Begindate) should be dtoc(var-"Begindate)

try:
Days = TABLECOUNT("cdr.dbf", "Between(cdr-"offhookdate,{"+ dtoc(Begindate) +"}, {"+ dtoc(Var-"EndDate) +"})")

Or better:
Days = TABLECOUNT("cdr.dbf", "Between_date(cdr-"offhookdate,Var-"Begindate,Var-"EndDate)")

This will fix your expression, but will give you the wrong information: it will counts all the dates between those 2 dates, not just the unique ones. If you want only the unique dates, that's a horse of a different color.

Gabe
P.S. I am not sure if you should enclose "+...+" in curly parenthises ! I rarely use it.

[spain246]

RE: Date Total on Report

Well for some reason I still can't get it to work. It loads and says it's a valid function, but how about this.
Just to make it easier, I have a begindate and enddate that I just need a day total on. The dates are entered as a query from a form.

Ex: 01/01/2000 (begindate global var) and 01/02/2000 (enddate global variable) = 2 days

Is there an easy way to do just this? I can then plug it into my other equation.

Thanks
Steve

(The report is built off a set. I just want the range from the entered variables)

[G Gabriel]

RE: Date Total on Report

Steve:
I am not clear on what you are asking:
First: What do you mean you can't get it to work? what do you get? Have you updated your variables? Is var-"enddate higher than var-"begindate?

Second: "I have a begindate and enddate that I just need a day total on"
I do not quite understand what you are after: do you want a "count" of the dates between the begindate & enddate? or do you want a count for all records that have any dates, begin or end?

In your example, you counted 2 days but one of them is a begindate and the other is an enddate? Dosn't each record have a begin and an end date? wouldn't the count in your example be 1 instead of 2? Are you trying to count the number of days Lapsed between the begin and end date for each record then total that? if that's what you want, it's quite a very simple matter.

I am totally confused as to what you want to achieve?
Gabe

[Melvin Davidson]

RE: Date Total on Report

Hey guys, you are missing the big picture!
What you want is the days difference between the earliest date and the latest date.
IOW, you need 3 calculated fields.

min_date = minimum(The_Date_Field)
max_date = maximum(The_Date_Field)
day_diff = calc-"Max_Date-calc-"Min_Date

This works if you put them in the appropriate place.

[G Gabriel]

RE: Date Total on Report

Melvin:
Maybe that's what Steve wants, or maybe what he wants (which would make more sense) is the day difference for each record then have all these diferences totaled. These are two different things.

I say it makes more sense since the min begindate might belong to one record(event) while the max enddate might belong to a completely diffreent event.

I can't surmise exactly what he wants or what is the essence of the application. It's all out of context.
Gabe

[spain246]

RE: Date Total on Report

Sorry Gabe, here is what i'm looking for in essence. I just need to set up a calculated field that counts the number of days between 2 dates. I have a query setup that let's the user query all of the data between any 2 dates. The user enters the Begin Date and the End Date. The query shows all of the data between these two dates.

Now, what I was looking for was a formula that would allow me to average some sales totals by day. For instance, I might have queried between 01/01/04 and 01/02/04. That is a 2 day period. I need to find a command that would allow me to display "2" in a field on my report. Or if I query from 01/01/04 and 01/01/05 I need it to display a "366".

This ultimately would allow me to average out my sales totals by day with the average command. That is what I am looking for and I am at a loss as to how to do this.

Thanks!
Steve Hall

[Melvin Davidson]

RE: Date Total on Report

Steve,

as previous indicated:

day_diff = End_date - Begin_Date

Average Sales would then be

Total_Sales / day_diff.

Not sure how you are getting total sales.
Perhaps
Total_Sales = TABLESUM( Lookup_Table as C, Filter as C, Lookup_Expression as C )

[G Gabriel]

RE: Date Total on Report

Steve:
As I anticipated, you are going to fix the expression but get the wrong information:
If two records have the same date: say 01/01/2005 and three records have 01/02/2005, tablecount() will give you a count of 5 when in reality there were only 2 dates.

The easiest way to do that would be as Melvin suggested with one modification, the formula should be:
"b"endate-begindate+1 "/b"since you have to include begindate.

That gives you the correct number of days to use in your averages.

Gabe

[G Gabriel]

RE: Date Total on Report

Steve:
As I anticipated, you are going to fix the expression but get the wrong information:
If two records have the same date: say 01/01/2005 and three records have 01/02/2005, tablecount() will give you a count of 5 when in reality there were only 2 dates.

The easiest way to do that would be as Melvin suggested with one modification, the formula should be:
endate-begindate+1 since you have to include begindate.

That gives you the correct number of days to use in your averages.

Gabe