4 Replies Latest reply: Feb 23, 2018 4:18 PM by Jose Gonzalez RSS

    Set Analysis

    Jose Gonzalez

      Hello, good if someone can help me please, I would appreciate it very much.

       

      I need that the day, month, year, date not only correspond to that variable, but to another that is called vMaxDay.

       

      Current code:

       

      sum ({<day, month, year, Date = {"$ (vLastDay)"}, FLAG_TITLE = {'S'}, CATEGORY = {'Available for Sale'}>} PRICE_MERCED)

        • Re: Set Analysis
          Digvijay Singh

          Do you mean Date needs to be compared with vLastDay AND vMaxDay? Are these variables assigned Date value in date format?

           

          sum ({<day, month, year, Date = {$(vLastDay)>*<day, month, year, Date = {$(vMaxDay)>+< FLAG_TITLE = {'S'}, CATEGORY = {'Available for Sale'}>} PRICE_MERCED)

          • Re: Set Analysis
            Jerry Jackson

            If Date is your transaction date and the variable vLastDay and vMaxDay are in the same format, you could try the following if you are trying to get data between the 2 dates:

             

            sum({<Date={">=$(vLastDay),<=$(vMaxDay)}, FLAG_TITLE = {'S'}, CATEGORY = {'Available for Sale'}>} PRICE_MERCED)


            Also check out creating a calendar table, How to create a Calendar

             

            Once you have a calendar table you can build variable for different time periods, like a prior MTD and just drop the variable into the set analysis.

             

            LET vPriorMTD = Replace('MonthID = {"@(=Max(MonthID) - 1)"}, Date =  {"<=@(=AddMonths(Max(Date), -1))"}','@','$');