Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
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
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))
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
Yes, intervalmatch could be the 'problem'. Could you post your sample app here?
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!!
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?
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
Hi Steafan
I am using QV11
and HW spwc is Intel i5 and 8 GB.
hope this is not the problem