Calender: from Hours to an aggregrated Day total in script

[#1]

Hi All,

I want have create a fieldname (Date for example)  which is resulting in  the sum of items sold in 24 hours.

I use the below mentioned script to create a calender  with hours .Sales should be registered per hour.

Temp_Data:

Load

date((($(#vMinDate) * 24) +(Iterno()-1))/24, 'DD-MM-YYYY hh:mm') as DateTime

Autogenerate(1)

While

($(#vMinDate) * 24) +(Iterno()-1) <= $(#vMaxDate) * 24;

Calender:

LOAD

             DatumTime,

     Year(DatumTijd)   as Year,

     Month(DatumTijd) as Month,

     ????                    as Date   ,                 What is the syntax for an aggregrated total  for a day. So the sum over the 24 hous in a day?

[swuehl · Accepted solution]

Not sure if I understood, what you mean with aggregated total for a day.

You could use

daystart(DateTime) as Date,

to get rid of the time part, so your calendar field Date will show date only values.

It seems to me that you create your calendar from your Temp_Data, of course you can do so, but then you will only have calendar values for DateTime values that are part of your Temp_Data  table (i.e. you might get missing values in list boxes for dates, months, years depending on your input data).

It might be better to create a master calendar (search the forum for some samples) for your required period, and then link this master calendar to your Temp_Data by Date (and create the Date field also in your Temp_Data using daystart() ).

Hope this helps,

Stefan

[#3]

Hi Stefan

Thanks alot.

What i mean is I want to use the fieldname DATE as dimension and my expression should give the total salesover the 24 hours  for that days selected.

I am now using daystart ()  as DATE to create the field DATE and that works, but now I am facing memory problems.

Do you know if this is something what has to do with the temp date calender or maybe my expression

Sum(aggr(Sales),PPRODUCT,DatumTime)

Thanks again for your correct answer

[swuehl]

How many memory does your application use and how big is your source data (or how many lines do you create in your temp_data)?

The aggr() is definitely somewhat memory consuming, but using a aggr() is not unusual (it seems you have a typo in your expression, please check your brackets).

In your expression, you group by Product and DatumTime, where DatumTime is very fine granular. Maybe you want to group by Product and Date, using sum(Sales) in your adv. aggr. expression?

=sum( aggr ( sum(Sales), Product, Date))

[#5]

Hi Stefan

file size is 5MB

and these are the line volumes

In my opinion nothing special heree.

But when I am including DatumTijd  then the application stops aand I have to restart QV again.

I also use intervalmatch in my script is that something what can cause the problem

[swuehl]

Yes, intervalmatch could be the 'problem'. Could you post your sample app here?

[#7]

In this example I have reduced the number of lines in order to ge the expression working

pls also note that the field names are a litlle bit different.

Thans for all the help, really appreciate it!!

[swuehl]

2perform,

I am pretty sure now that your intervalmatch is causing the memory problems. If I read your tables correctly, you have some time periods (for clinical OPs or something), AfdOp, AfdAf. You then link DateTimes from your calendar to the time periods, which can pretty fast create a large synthetic table (since your DateTime calendar is quite granular).

The most important questions is, what are you trying to achieve? Maybe there is some better performing alternative.

edit:

But your overall memory consumption giving a file size of 5 MB should not be a real problem. Using what system (QV version / HW specs) do you develop?

[#9]

I need to know how many care a patient needs. This depends on the moment  the patient is at the ward.

Therefor we have 3 types of services  A, B and C

a normal wordkday  between 8 - 5  = B, at night = A and evening =C

In weekend   between 8 - 5  = C al other = B.

C

Table 1

Patient Diagn   AfdOp                  AfdAf

1          XXXX    4-6-2012 16:00     4-6-2012  17:45

Table  2

DatumTijd              ServType

....            

4-6-2012  16:00             B

4-6-2012  17:00             C

.........

Table  3

CW= Careweight

Diagn    CW A   CW B   CW C

XXXX      0.5       1       0,75  

Patient  Diagn   CW A    CWB   CW C         DatumTijd            ServiceType    CareQtY

1           XXXX       0.5       1       0,75            4-6-2012  16:00      B                    1        ServiceType = B therefor CW = B = 1

1           XXXX       0.5        1       0,75            4-6-2012  17:00     C                    0,75   ServiceType = B therefor CW = C = 0,75  

So the result of dimension DATE 4-6-2012 with the expression sum(aggr(CareQTY),DatumTijd,Patient)) = 1,75

I hope this makes it clear to you what I want to achieve

[#10]

Hi Steafan

I am using QV11

and HW spwc is  Intel i5 and 8 GB.

hope this is not the problem