2 Replies Latest reply on May 10, 2017 7:43 PM by Elliott Stam

    What's the best way to "isolate" a value with a FIXED LoD?

    Luis Vicentini

      Or in more general terms, what's the best way to write a FIXED LoD expression?

       

      Imagine that we have a table with monthly values for X, and that we want to compare the variance between the first X value in January vs the same X value in each following month.  What's the best way to go about this calculation?

       

      My usual way would be through three steps in separate calculated fields, the first one to give me only the value for January with nulls for all the other months, and then the FIXED LoD to have the January value available for comparison with all the other months.

      To illustrate -

      Calculated Field 1 "January and Nulls": If [month]="January" then [Value] end

       

      Calculated Field 2 "January LoD": {FIXED: max([January and Nulls])}

       

      Calculated Field 3 "January DateDiff": DateDiff('day', [Value], [January LoD])

       

      And that would give me the difference between January's value and whichever other month's value.

      Is it possible to write fields 1 and 2 together? What's your best practices for writing LoDs or to isolate values for comparisons across time or other dimensions?

      Thanks everyone.

        • 1. Re: What's the best way to "isolate" a value with a FIXED LoD?
          Sujay Paranjpe

          Hi Luis,

           

          For the below statement and sample data in Table below, are interested in comparing first day's sale of each month with first day sale of January?

          Imagine that we have a table with monthly values for X, and that we want to compare the variance between the first X value in January vs the same X value in each following month.  What's the best way to go about this calculation?

           

          Date | Sales

          01-Jan | 300

          02-Jan | 400

          07-Jan | 1000

          01-Feb | 500

          03-Feb | 1700

           

          Best,

          Sujay

          • 2. Re: What's the best way to "isolate" a value with a FIXED LoD?
            Elliott Stam

            Hi Luis,

             

            As far as best practices go, to me that means keeping things readable and understandable to the point that I could pick the workbook up again a couple months down the road and easily get a feel for what my past self was up to. Typically, if I do everything in one calculation then I will see my ability to decipher what I did plummet.

             

            Now I'm not sure whether you're trying to get a date difference between your max date and some other date, or whether you're trying to compare a measure value from your latest date in January to another day's measure value (the formulas would be considerably different), but you can let me know the specifics if this doesn't make enough sense.

             

            With that said, in your case you could do formulas 1 and 2 in one formula kind of like this:

             

            { FIXED DATENAME('month', [Date]):

            MAX(

            IF DATENAME('month', [Date]) = "January"

            THEN

            [Date]

            END)}

             

            What I often do if I know I have a lot of date calculations to do is calculate those date values individually in their own reusable formulas. For example, I often need to know what my latest date is, as well as what the previous month was from that date, and so on.

             

            So I would make a field called 'Latest Date':

            { FIXED : MAX( [Date] )}

            this formula would ALWAYS have my latest date in it... then I might make another field called "Previous Month"

             

            DATEADD('month', -1, DATETRUNC('month', [Latest Date])

            this formula would look at what month the latest date occurred in, and subtract one month from that (thus providing the previous month).

             

            I think the best practice is to keep everything easily readable, and to set yourself up so that you don't have to repeat the same calculation over and over again.

            They call it the DRY principle (Don't Repeat Yourself) versus WET (Write Everything Twice). Totally up to you, and whatever works for you is the right answer.

             

            Hope that helps,

             

             

            Elliott Stam - InterWorks