4 Replies Latest reply: Sep 19, 2011 8:34 AM by Stefan Wühl RSS

    Set analysis: multiple selections in one field

      Hi all,

       

      I would like to count something if the archief_month is smaller than 1 field and equal to the reporting date field.

       

      Any ideas how to do this with set analysis? (could not find it in the help)

       

      This does not work I think:

       

      count({< archief_month = {"<=archief_verwacht_month", reporting_date} >}  something )

       

      Thank you very much

        • Re: Set analysis: multiple selections in one field
          Stefan Wühl

          Hi Martijn,

           

          I would assume that above expression won't work anyway in a set expression because you want to compare on record base if achief_month e.g. equals reporting_date, do you?

           

          If so, I would try

          =count(if (achief_month <= achief_verwacht_month and achief_month = reporting_date, something))

           

          If you have difficulties with performance, you may put a flag in your script created by above expression and then do

          =count({<flag = {1} >} something)

           

          Hope this helps,

          Stefan

            • Set analysis: multiple selections in one field
              Stefan Wühl

              Hi Martijn,

               

              just changed my mind, try something like

              =count({< archief_month = {"=archief_month <=archief_verwacht_month and archief_month=reporting_date"} >}  something )

               

              (BTW, I assume it is possible to compare a reporting DATE with a archief_MONTH, right?)

               

              Regards,

              Stefan

                • Set analysis: multiple selections in one field

                  Hi Stefan,

                   

                  You are correct, archief_month <=archief_verwacht_month are 2 fields in a table which should be analyzed per row of this table, because they both belong to 1 fact. So I agree with your first reply. (but then, not off course with your second right?)

                   

                  reporting date is a global variable, independent of tables.

                   

                  yes all dates are in the correct format, a month is the first day of the month like 1-1-2011

                   

                  I will try also your second one, but in theory is was a wrong one because of the row by row comparison?

                   

                  thanks a lot,

                   

                  regards Martijn