1 Reply Latest reply on Apr 23, 2018 2:41 PM by Joe Oppelt

    First and last value for a dimension that is not shown

    Abiel Reinhart

      I have monthly data with the date, product type, beginning balance, inflow, outflow, and ending balance (six columns of data). When aggregating data over multiple periods I need to always show the beginning balance summed across products for the first month and the ending balance summed across products for the last month. The Date field might not be on the worksheet at all, or I may be viewing the data rolled up by year or year/quarter. If Date is not being used as a dimension then I'm looking for the beginning balance on the first month of the entire dataset, whereas if I'm viewing data by year I'm looking for the beginning balance on the first non-null month of each year, and if I'm viewing by year/quarter I'm looking for the beginning balance on the first non-null month of each year/quarter (and likewise for the ending balance, which would be on the last month).

        • 1. Re: First and last value for a dimension that is not shown
          Joe Oppelt

          There are a lot of ways to do what you want to do, and the best way depends on what you are actually doing.

           

          In the attached I expanded your sheet to MONTHS.  Then for sheet (2) (which is just a copy of sheet 1) I created two calcs.  See [First Month] and [Last Month].  The FIXED calcs look at your full set of data, and these are finding your first month and last month from among all the data in your data source.

           

          Addressing the "lot of ways", I'm doing a DATETRUNC on your DATE field to do my MIN and MAX at the month level.  DATETRUNC truncates a date to the beginning of the time period specified in the calc.  So I'm truncating to MONTH.  Therefore data on Jan1 and data on jan31 all get treated by the calc as Jan1 for the sake of simplicity.

           

          Now look at what I did in [First and last ending balance].  This gets evaluated at the data row level, and I duplicate the [Ending Balance] value if the row has a date that is either in the first month or the last month.  All other rows will be NULL.  When I add this calc to the text shelf, you can see that the value for "End:" is replicated in the first and last months.

           

          So at a high level, you could make [First Month] and [Last Month] conditional, such that either you get the MIN and MAX in the whole data set, or you get the MIN and MAX for the year you are processing.

           

          An example of getting the MIN for 2019 is:

           

          {FIXED : MIN( if YEAR([Date]) = 2019 then DATETRUNC('month',[Date]) END) }

           

          Of course, you wouldn't be hard-coding 2019 in there.  Perhaps you have a parameter where the user picks a year or something like that.  It depends on what you are actually doing in your dashboard.  But the examples shows that you can have "IF" logic within the MIN() function, and by extension, within the FIXED calc.  And if you need that to be selected by a filter instead of a parameter, you can make your filter talk to a FIXED calc by adding it to context, or by using an INCLUDE or EXCLUDE LOD instead of a FIXED LOD.