5 Replies Latest reply on Apr 7, 2016 3:46 PM by Bill Immer

    How to compare a date dimension to it's min value?

    Bill Immer

      Greetings,

       

      I am trying to create a calculated field that show "sales" only for items where the invoice date's year matches the min invoice dates year:

       

      IF DATEPART('year',[Inv Date]) = DATEPART('year',min([Inv Date])) then

          SUM([Inv Sales])

      ELSE

          0.0

      END

       

      If I aggregate all and put ATTR() around the [Inv Date] I don't get any results because there is more than one value for [Inv Date] at my level of detail.  If remove all aggregation, then I don't know the min year.

       

      I could use fixed level of detail and make it work but I want this calculation to be used for any combination of dimensions.

       

      What is the best way to make this work?

       

      Thanks!

      --bill

        • 1. Re: How to compare a date dimension to it's min value?
          Shinichiro Murakami

          Either of below two formulas work.

          But first one is much easier to handle.

           

          [Sales only Min year]

          if {fixed:min(year([Inv Date]))}=YEAR([Inv Date]) then [Inv Sales] END

           

          [Sales only Min year (sum)]

          if attr({fixed:min(year([Inv Date]))})=attr(YEAR([Inv Date])) then sum([Inv Sales]) END

           

          Thanks,

          Shin

           

          9.0 attached,

          • 2. Re: How to compare a date dimension to it's min value?
            Bill Immer

            Thank you for your reply,

             

            I guess I was not clear in my post as I am trying to find the min([Inv Date]) for any dimension combination.  So {fixed:min(year([Inv Date]))} returns the min year of the data source and not the dimensions in the vis.  For example, I could use

             

            {fixed [Cust ID] : min(year([Inv Date]))},

             

            but this only works for one situation.  I was trying to create something generic.  So that it would show the correct result for any dimension combination.  I could make a few dozen of these for my dimension combinations but was looking for another way.  This seems like it should be easy but I haven't been able to get it to work.

             

            Also I can't use attr(YEAR([Inv Date])) as it returns an * for almost any combination of dimensions.

             

            Thanks,

            --bill

            • 3. Re: How to compare a date dimension to it's min value?
              Shinichiro Murakami

              Still not sure how you want to get "MIN" value with the statement of "any combination".

              The context filter might help the situation?

               

              Filters and Level of Detail Expressions

               

              FIXED

              FIXED level of detail expressions compute values using the specified dimensions without reference to the view level of detail—that is, without reference to any other dimensions in the view.

              FIXED level of detail expressions also ignore all the filters in the view other than context filters, data source filters, and extract filters.

              Example: { FIXED [Region] : SUM([Sales]) }

              • 4. Re: How to compare a date dimension to it's min value?
                Bill Immer

                Thanks,

                 

                This is for a dashboard where I am calculating a annual growth rate of sales.  When the dashboard is accessed, the user see the AGR for the company, beginning at year 2009, which is the oldest data in the set.

                 

                From here, the user can drill into a warehouse, sales region, outside sales rep, vendor line, industry group.  And from here they can drill into customers and product lines.

                 

                for any drill-down combination I need to calculate the AGR.  For example if I drill into a specific sales rep/warehouse combination, I may need sales from 2012 when the location or customer became active.

                 

                But I think you may have the answer.  That I may be able to use {fixed : min(year([inv sales]))} if I add all of my worksheet filters to the 'context'.  I had forgotten about this!

                 

                Yes!  I just tested and a combination of a fixed level of detail and filters added to context did the trick!

                 

                Thanks so much,

                --bill

                • 5. Re: How to compare a date dimension to it's min value?
                  Bill Immer

                  One more update.  Adding my filters to the context did not work in the end.  What worked was to make my LoD's finer rather than coarser:

                   

                  This worked - where I left the "min" at the vis LoD, and used "include" on invoice date when grabbing sales from records....

                   

                  IF {INCLUDE : YEAR(MIN([Inv Date])) } =

                  {INCLUDE [Inv Date] : YEAR(MIN([Inv Date]))  } THEN

                       [Inv Sales]

                  ELSE

                       0

                  END