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,
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
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))
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!!
example.qvw 2.3 MB
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.
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.
Patient Diagn AfdOp AfdAf
1 XXXX 4-6-2012 16:00 4-6-2012 17:45
4-6-2012 16:00 B
4-6-2012 17:00 C
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
I noticed that you are performing a left join to your OPNAME_OPNMUT table using the same resident table, apparantly just to add some fields based on some other field values.
This table is also pretty large, right? A join will (temporarily) need huge amount of memory, so it should be avoided on large tables unless really needed. I would assume that you can create your new fields also in a resident load of your OPNAME_OPNMUT table.
Second, you assign 17:00 to night shift, I think that's a bit early
If first issue is not your issue, where does your script stop? What table is being loaded then?