11 Replies Latest reply: Jun 9, 2012 5:33 AM by 2perform RSS

    Calender:  from Hours to an aggregrated Day total in script

      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?

       


        • Calender:  from Hours to an aggregrated Day total in script
          Stefan Wühl

          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

            • Calender:  from Hours to an aggregrated Day total in script

              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

                • Calender:  from Hours to an aggregrated Day total in script
                  Stefan Wühl

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

                    • Calender:  from Hours to an aggregrated Day total in script

                      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

                       

                      Data loadingJPG.JPG

                       

                       

                      export.png

                        • Calender:  from Hours to an aggregrated Day total in script
                          Stefan Wühl

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

                            • Re: Calender:  from Hours to an aggregrated Day total in script

                              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!!

                                • Re: Calender:  from Hours to an aggregrated Day total in script
                                  Stefan Wühl

                                  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?

                                   

                                   

                                    • Re: Calender:  from Hours to an aggregrated Day total in script

                                      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

                                      • Re: Calender:  from Hours to an aggregrated Day total in script

                                        Hi Steafan

                                         

                                        I am using QV11

                                        and HW spwc is  Intel i5 and 8 GB.

                                         

                                        hope this is not the problem