4 Replies Latest reply: Jan 13, 2012 7:58 AM by Henric Cronström RSS

    How to count certain posts in a datefield?

      I hope someone can help me with a exprecssion for the count of just ceratin post in my datefield.

      When I select month I get all post in the datekey that have something to do with this month. But how can I in a chart just show the ones that have a end date of the selected month?   Sort of Count({'Datekey=*_2011-Current selectedMonth})%DateKey) and vice vers for started same month?

       

       

      qw123.JPG

      All suggestions and hints are welcome! Thanks

        • Re: How to count certain posts in a datefield?
          Teemu Pitkänen

          Hey,

           

          You could try creating a variable defined as follows:

           

          vSearch     = '"*_' & Only(Year) & '-' & Only(Month) & '-*"'

           

          You need to figure out how to get the month in numeric format though Then in your chart you can use something like Count( {< Key = {$(vSearch)} >} Key).

           

          A more efficient way would be to check in the script if the year-month matches the last part of the key field and flag rows that do. Then the expression would be something like Count( {< Flag = {1} >} Key). Make another similar flag for the first part if needed.

           

          -Teemu

            • How to count certain posts in a datefield?

              Hi

               

              Can you explain the expression Count(if(len(trim(subfield(%DateKey,'_',2)))>0,%DateKey)) a bit more Henric?

               

              I´m new to QV and not´t  that good at expressions. =)

               

                • How to count certain posts in a datefield?
                  Henric Cronström

                   

                  The subfield function is used to pick out a substring from a field value that is in fact a list of values. So the subfield(%DateKey,'_',1) call will interpret the %DateKey as a list of field values separated by underscores (specified by the 2nd parameter) and return the first value in the list (specified by the 3rd parameter). Which is your start date.

                   

                  Then you can use this information in different ways. You can for instance test if there is a start date at all:

                   

                  if(len(trim(subfield(%DateKey,'_',1)))>0,...)

                   

                  and you can (in a chart or text box) count the %DateKeys that lack a start date:

                   

                  Count(if(len(trim(subfield(%DateKey,'_',1)))=0,%DateKey))

                   

                  or you can count the %DateKeys that have end dates:

                   

                  Count(if(len(trim(subfield(%DateKey,'_',2)))>0,%DateKey))

                   

                  Does this make sense?

                   

              • How to count certain posts in a datefield?
                Henric Cronström

                 

                There are several ways to do this using string functions. One way could be

                 

                Count(if(len(trim(subfield(%DateKey,'_',2)))>0,%DateKey))

                 

                 

                where the subfield function extracts the string found after the underscore character.