3 Replies Latest reply: Oct 2, 2017 8:49 AM by Felip Drechsler RSS

    Understanding specific variables

    Jonah Van Quekelberghe



      Someone made the following variable but I can't seem to interpret correctly how it works.

      s.ClearCalanderFields : ='['&concat({<$Table={'Calendar'}>}$Field,']=,[')&']='

      If I have a Table "Calander" with fields MonthYear, Year and MonthSerial_ in it, would the result be [MonthYear]=,[Year]=,[MonthSerial_]=,[]=

      What does this variable exactly do?

      After that I have another variable

      f.SetYTD  : $(s.ClearCalendarFields),Year= {$(=max(Year)-$1)}, [MonthSerial_] = {"<=$(=Max([MonthSerial_])-($1*12))"}

      This starts with the previous Variable and adds some other elements : pick Max Year and pick max MonthSerial and the ones before it. How does this exectly work? What is the output?

      The idea was to use this Variable in a Set Analysis like : sum( {<f.SetYTD(0)) >} Sales) to limit the data to only Year to Date Sales. When doing sum( {<f.SetYTD(1)) >} Sales) it would give the Year to Date Sales of the previous year. How does this mechanisme work that when i put 1 it gives the YtD sales of the previous year?

      Thanks in advance for the help!


        • Re: Understanding specific variables
          Felip Drechsler

          Hi Jonha,


          The first variable s.ClearCalanderFields, only gets the fields of the specified table, on your case Calendar, so by making that, you'll be able to tell that it has the

          • [MonthYear]
          • [Year]
          • [MonthSerial_]
          • []



          The second one, f.SetYTD is using the first one as clearing the fields on the set analysis that is used in an expression.


          So, by expanding the dollar sign on the f.SetYTD variable, you'll get


          [MonthYear]=,[Year]=,[MonthSerial_]=,[]=,Year= {$(=max(Year)-$1)}, [MonthSerial_] = {"<=$(=Max([MonthSerial_])-($1*12))"}


          So what it's actually doing is ignoring the


          fields, and then setting the others like:

          Year= {$(=max(Year)-$1)}, [MonthSerial_] = {"<=$(=Max([MonthSerial_])-($1*12))"}


          In my opinion, the first parameters shouldn't need to be duplicated, since you're setting them anyways, must be some logic to it, but i'm not seeing why they're duplicated, could be only something like:


          sum({<[MonthYear]=,[]=,Year= {$(=max(Year)-$1)}, [MonthSerial_] = {"<=$(=Max([MonthSerial_])-($1*12))"}>}Sales)

          (In this case, the [MonthYear]=,[]= would be ignored, while the other fields would be set according to the expression.

          For example, if you want a sum of sales ignoring the fields of the calendar, you would do




          and for year to date


            • Re: Understanding specific variables
              Jonah Van Quekelberghe

              Hello Felip,


              Thank you for the helpful response. I have 2 more questions though :


              1. How does in a Set Analysis the $(f.SetYTD(0)) give the Year to Date of this year, and the $(f.SetYTD(1)) give the Year to Date of past year? What part of the forumula does this and how does this work?
              2. In the s.ClearCalanderFields, do I have it right that it'll always create at the end following part "[]=" (an empty field), does this have any impact on my data?



                • Re: Understanding specific variables
                  Felip Drechsler

                  Hi Jonah


                  1. This part of the code


                  [MonthSerial_] = {"<=$(=Max([MonthSerial_])-($1*12))"}

                  will get the parameter (in this case $1 is a parameter you can input) and multiply it *12 and take it of the MonthSerial max, so in this case, if you do $(f.SetYTD(0)) its, making

                  [MonthSerial_] = {"<=$(=Max([MonthSerial_])-(0*12))"} // Getting the year to date

                  as to $(f.SetYTD(1))

                  [MonthSerial_] = {"<=$(=Max([MonthSerial_])-(1*12))"} // Getting the previous year

                  and thus, giving the previous year amount.

                  2. For the [] field, I can't quite understand what it does on your data model, maybe its a control flag of some sort.

                  See if there's any data for this field.

                  In my opinion if it's empty for all cases, it can be taken out.