3 Replies Latest reply: Aug 3, 2018 2:32 PM by William Fu RSS

    Using Master Item In Set Analysis

    Brian Droesch

      Thanbk you

      I currently have a dashboard on an app that shows a metric over a trended period of time in a pivot table. The user selects date from the master item dimension called “Data As Of Date” and the number of months that they would like to look back. In my pivot, my columns are Data As Of Date and my measure is shown in the formula below where [Value] is the metric I am trying to show over time.  So if the user selects “Data As of Date” = 05/31/2018 and a window of 12 months then the pivot table will only show 06/30/2017 to 05/31/2018.

       

      Measure in Pivot

      =Only  ({<[Data As Of Date]={">=$(=AddMonths(vDynamicMaxDate,-vMonthWindow+1)) <=$(=MonthEnd(vDynamicMaxDate))"}> } [Value])

       

      Variable

      Formula

      vDynamicMaxDate

      =Max(AsOfDate)

      vMonthWindow

      12

       

       

      This formula works in my current app which is on version 3.2 but I am upgrading to the April 2018 version and it appears to have some issues. When I try it on the new version, it just shows the 1 month that the user selected for Data As Of Date (and not the entire window).

       

       

      Can I not reference “Data As Of Date” in this set analysis expression because it is a Master Item? The Master Item formula is Data As Of Date = Date(AsOfDate,’MM/DD/YYYY’). AsOfDate is a date field that is in a table with the field [Value] that I am trying to show in the pivot. I found that in the new version, the chart works if I select a value for AsOfDate (not Data As Of Date) and I change the formula to the following:

       

      =Only  ({<[AsOfDate]={">=$(=AddMonths(vDynamicMaxDate,-vMonthWindow+1)) <=$(=MonthEnd(vDynamicMaxDate))"}> } [Value])

       

      I really want the users to be able to use the “Data As Of Date” dimension instead as that is the field that they use across the entire app and AsOfDate shows ‘M/DD/YYYY’ instead of ‘MM/DD/YYYY’.

       

      Thank you,

      Brian

        • Re: Using Master Item In Set Analysis
          William Fu

          What if you edit the DateFormat part of the script instead?

           

          SET DateFormat='MM/DD/YYYY';

            • Re: Using Master Item In Set Analysis
              Brian Droesch

              Hi William,

               

              I believe that would work if I then changed the formula of Data As Of Date to be Data As Of Date = [AsOfDate].

               

              My concern is if I have other sheets that are relying on AsOfDate being in the other data format. Also with this new Master Item formula, when I select Data As Of Date using a filter on the sheet it shows AsOfDate in the selection bar at the top and not Data As Of Date.

               

              Do you know why there would be a difference between formula working in the old version compared to the April 2018 version?

               

              Thank you,

              Brian

                • Re: Using Master Item In Set Analysis
                  William Fu

                  Sorry, not sure why that would happen or if it's specific to April 2018

                   

                  I tried to replicate your example myself and it seems to work: is this what you're looking for?

                   

                  The chart uses set analysis expressions involving "DtField", while "NewField" is a master dimension (Date(DtField, 'DD/MM/YYYY')).

                   

                  I can filter a value in the NewField dimension and the chart is responsive. It also displays correctly on the selections bar.

                  sample.png