6 Replies Latest reply: Dec 12, 2011 6:02 AM by jagan mohan rao appala RSS

    Sum based on begindate and enddate

      Suppose i have 2 dates and a person(member):

      Date begin = 01-04-2011

      Data end = 31-07-2011

      Person

       

      i want to make a barchart that counts the member in the months 4, 5, 6 and 7 based on the date begin and date end.

       

      Example:

       

      Month     Sum

      1               0

      2               0

      3               0   

      4               1

      5               1

      6               1

      7               1

      8               0

      9               0

        • Sum based on begindate and enddate
          Sunil Chauhan

          use below format

          sum({<Date={">=$(min(Date)<=$(max(Date)"}> amount)

           

          hope this help

            • Re: Sum based on begindate and enddate
              Dennis Hoogenboom

              Sunil solution will work when you have only one date field. But you say you have 2 date fields.

              Take a look at the attached file for a solution with variables and a button with a macro.

               

              Let me know if this works for you.

               

              Good luck.

                • Sum based on begindate and enddate

                  Unfortunately i can't open the document in my personal license. I have understand that there is a standard solution for this problem. I don't think i'm the first user who is facing this problem. Every HR department is interested in these kind of charts. Thanks anyway for your time!

                    • Re: Sum based on begindate and enddate
                      Dennis Hoogenboom

                      I am going to do this in Dutch...

                       

                      Goedemorgen.

                       

                      Ik zal proberen uit te leggen wat ik gedaan heb aan de hand van een vraag van giozedzed1 hier:

                      http://community.qlik.com/message/168502#168502

                       

                       

                      1 Als je dat Excel bestand gebruikt kun je het volgende script gebruiken:

                       

                      Data:

                      LOAD name,

                           number,

                           Afdeling,

                           Salaris,

                           birthday,

                           Datumindienst,

                           IF(ISNULL(Datumuitdienst) , 'Nog in Dienst' , Date(Datumuitdienst)) as Datumuitdienst

                      FROM

                      [Kopie van personeel.xls]

                      (biff, embedded labels, table is Blad1$);

                       

                      2 Maak daarna 2 variabelen aan (CTRL+ALT+v)

                       

                           vStartDate

                           vEndDate

                       

                      3 Maak een inputbox met bovengenoemde variabelen

                       

                      4 Maak dan onderstaande macro (CTRL+m)

                       

                      sub SelectDatum

                       

                          ActiveDocument.ClearAll false

                          First= ActiveDocument.Variables("vStartDate").GetContent.String

                          Last= ActiveDocument.Variables("vEndDate").GetContent.String

                          mySelect= ">=" & First & " <=" & Last

                          ActiveDocument.Fields("Datumindienst").Select mySelect

                          ActiveDocument.Fields("Datumuitdienst").Select mySelect

                          ActiveDocument.GetField("Datumuitdienst").ToggleSelect "Nog in Dienst"

                       

                      end sub

                       

                      5 Maak een button met een actie welke de macro start

                       

                           Properties >> Action >> Add     :      External     >> Run Macro >>OK

                           Marco Name:  SelectDatum

                       

                      Vul in de inputbox een begindatum (vStartDate) en einddatum in (vEndDate) en klik op de button om de selectie uit te voeren.

                       

                      De macro zorgt dat de twee datums welke ingevuld zijn een selectie range worden welke toegepast worden op de velden Datumindienst en Datumuitdienst

                      De laatste regel in het script is optioneel, deze voegt de waarde "Nog in Dienst" toe aan de selectie op het Datumuitdienst. ("Nog in Dienst" heb ik in het script toegevoegd waar geen waarde ingevuld was, dit om hier toch een selectie op te kunnen maken)

                       

                      Laat me even weten of je hier iets mee kan.

                       

                      Succes,

                      Dennis.

                        • Sum based on begindate and enddate

                          Dank je wel. Ik begrijp de strekking, maar kom er nog niet helemaal uit. Overigens is dit wel precies de excelsheet die de vraag dekt.

                          Stel nu dat ik op basis van de sheet een grafiek wil hebben van alle maanden in 2000 met per maand het aantal medewerkers in dienst. Ik kan de link nog niet helemaal leggen omdat ik ook geen teller zie in je voorbeeld (of gebruik je "Number"?)

                            • Re: Sum based on begindate and enddate
                              jagan mohan rao appala

                              Hi,

                               

                              Check the attachment for the solution.

                               

                              Script:

                               

                              Temp:

                              LOAD

                              MakeDate(Year(Today()), MonthName, 1) AS tDate,

                              Num(MakeDate(Year(Today()), MonthName, 1)) AS tDateNum,

                              MonthName,

                              Value;

                               

                              LOAD * INLINE [

                                 MonthName,     Value

                               

                              1, 10

                               

                              2,               20

                               

                              3,               30 

                               

                              4,               10

                               

                              5,               15

                               

                              6,               13

                               

                              7,               1

                               

                              8,               20

                               

                              9,               20

                              ];

                               

                              Variables:

                              vStartDate - Assign to Calendar Control 1

                              vEndDate- Assign to Calendar Control 2

                               

                              Calculation Expression:

                              =Sum({<tDateNum={'>=$(=vStartDate)<=$(=vEndDate)'}>}Value)

                               

                              Hope this helps you.

                               

                              Regards,

                              Jagan.