5 Replies Latest reply: Jul 25, 2014 1:28 PM by Sebastian Pereira RSS

    Using a variable for full set analysis string

    Rory Webber

      Hi,

       

      I'm building a table that has a lot of set analysis work in it. An example of one of my field calculations below:

       

      =if(vDateType='Service',

        count({$<SERVICEDATE={">=$(=vFromDate) <=$(=vToDate)"},BDT_BOOKINGSTATUS={"CONFIRMED"}>}TOURREF),

        count({$<BOOKINGDATE={">=$(=vFromDate) <=$(=vToDate)"},BDT_BOOKINGSTATUS={"CONFIRMED"}>}TOURREF)

      )

       

      (I have two distinct date fields per record - SERVICEDATE and BOOKINGDATE on each record of my fact table. Requirement is to be able to select a From and To date and then identify if this should apply to the SERVICEDATE or BOOKINGDATE field and build various values around that.

       

      I'm going to be reusing this set analysis across multiple objects, so was wondering if there was anyway of capturing all the set elements  ( {$<SERVICEDATE={">=$(=vFromDate) <=$(=vToDate)"},BDT_BOOKINGSTATUS={"CONFIRMED"}>} ) into two variables (one for SERVICEDATE, one for BOOKINGDATE). That way if I ever need to change this in the future I can change in one place to affect multiple charts etc.

       

      Tried a few things, but just can't seem to make this work.

       

      Thanks,

       

      Rory.