6 Replies Latest reply on Aug 18, 2018 5:59 AM by Abhaas Mohan

    Display data from latest month to avoid duplicates

    Abhaas Mohan

      Hi all,

       

      I have a query which revolves around displaying data points in a chart from the most latest month. I have attached part of my actual workbook which displays this problem.

       

      The actual workbook has multiple worksheets (maps, charts etc), and the data presented on those is supposed to be aggregated across all months ( such as average sales, average market share). This dashboard is to update monthly with new data, so there are going to be multiple blocks of data, with differing dates (1 Jul, 1 Aug, 1 Sep, etc etc), with a from-to date parameter filter on it to drill down to a particular time duration. However, there is one sub-chart, which is supposed to show the number of network point as of the latest month of data.

       

      Currently, this value is coming as an aggregate (in sample twbx, Bihar has 41 billing point (BP) for X in July, and 40 in August.) Ideally, it should display 40, instead of 81.

       

      I have an idea, that there should be a calculated field which looks up whether the date of a particular location (Which is standardized) is the max among the data set, and return the #BP/TP for those location, but not sure how it would work.

       

      Thanks in advance!

        • 1. Re: Display data from latest month to avoid duplicates
          Simon Runc

          hi Abhaas,

           

          So one way to do this is as follows

           

          I first created a calculation to only be true for the last month of the data

          [Last Month of Data]

          DATETRUNC('month',[Data Date])

          =

          DATETRUNC('month',{MAX([Data Date])})

           

          I then used this to create a field that only contains the last months value

           

          [X Bp# Last Month]

          IIF([Last Month of Data],[X Bp#],NULL)

           

          hope that helps and makes sense

          1 of 1 people found this helpful
          • 2. Re: Display data from latest month to avoid duplicates
            Abhaas Mohan

            Thank you Simon Runc

             

            That solved the first part of the issue. However, it kind of restricts me from adding more date based customizations.

             

            Case in point - If I want to give the user the option of seeing historical data, I added parameters for Start/End Date & field filter (Data Range). I accordingly customised the IIF formula by replacing the null part with additional nested IIF with this Data Range. Now when I select any particular month (1 Jul - 31 Jul), I get that month's data.

             

            (

            IIF([Network Type] = "Billing Point" and [Last Month of Data],[X Bp#],IIF([Network Type] = "Touch Point" and [Last Month of Data],[X Tp#],

                 IIF([Network Type] = "Billing Point" and [Data Range], [X Bp#], IIF([Network Type]= "Touch Point" and [Data Range],[X Tp#],0))

            ))

             

             

            however, resetting the calendar (1 Jul - 31 Aug) results in your latest month solution breaking, with the measure returning a cumulative value of 81 instead of actual 40.

             

            I have added the updated workbook. Would be great if you could point out the error in my approach here.

             

            Thanks,

            Abhaas

            • 3. Re: Display data from latest month to avoid duplicates
              Simon Runc

              So I'm not sure how the parameter is supposed to "know" if it is to be used (i.e. user wants to see history), or ignored (i.e. only show last month).

               

              In the attached I've added a parameter so the user chooses last month, or custom date range.

               

              I can then use this to create the filter depending in the value

              [Selected Last Month/Custom Filter]

              IF [Last Month or Custom Dates] THEN [Last Month of Data]

              ELSE [Data Range] END

               

              and then bring this only the filter shelf and set to true.

               

              Now as we are using a filter to filter the data to the right level...we no longer need to apply the filter within the formula, so can just use

              [X Network_SR]

              IF [Network Type] = "Billing Point" THEN [X Bp#]

              ELSEIF [Network Type] = "Touch Point" THEN [X Tp#]

              END

               

              and similar for Y and Z.

               

              Hope that helps, and you can adapt this to how you want it to behave.

              1 of 1 people found this helpful
              • 4. Re: Display data from latest month to avoid duplicates
                Abhaas Mohan

                Hey Simon,

                 

                Thanks for this,

                 

                While this makes the User Interface clearer, it doesn't seem to work in the manner of how I wish for it to behave.

                 

                For example, let's say the user has Custom date selected. Here, the dates are 1 July to 31 Aug. The output measure X here gives me 81, which is the cumulative of X BP across two months. However, what I am aiming for within this date period, is still the latest month value within the custom range (which is 40). Ideally, in this scenario, the answer to both 'Last Month', and 'Custom Dates' would be 40.

                 

                I tried using this formula within the measure -

                { FIXED DATETRUNC("month",MAX([End Date])) : SUM(if [Network Type] = "Billing Point" THEN [X Bp#]

                ELSEIF [Network Type] = "Touch Point" THEN [X Tp#]

                END)

                }

                 

                I figure there needs to be a calculated field with some MAX aspect of End Date, such that for whatever the Date Range is selected, the FALSE of [Selected Last Month/Custom Filter] returns

                Max  of [Custom Date Range].

                 

                I hope this is clear.

                 

                P.S.- I can't do anything to Date Range or this Start-End Date parameter since this is required for the other worksheets.

                • 5. Re: Display data from latest month to avoid duplicates
                  Simon Runc

                  Ah so whatever the parameter date range, we want the last months value....

                   

                  In which case we just need to make a small tweak from the initial solution.

                   

                  We can forget the Custom/Last Month parameter, and just do the calculations like this

                   

                  [X Network_SR]

                  IF [Network Type] = "Billing Point" THEN IIF([Last Month of Data],[X Bp#],NULL)

                  ELSEIF [Network Type] = "Touch Point" THEN IIF([Last Month of Data],[X Tp#],NULL)

                  END

                   

                  following the same logic for Y and Z.

                   

                  We can then add your [Data Range] field to the filter and set to true, and the tweak we need to make is that is needs to be a "context" filter (click on the filter and select "Add to context", it will go gray/brown to indicate it's now a context filter). As FIXED LoDs (which we use to pick up the MAX month are computed before regular filters are applied, by making this filter a context one, it bumps the filter up the calculation pipeline so the date filter is applied before the LoD, so the new MAX Month is determined by the filter (not over the whole data set)

                   

                  Hope that does the trick

                  1 of 1 people found this helpful
                  • 6. Re: Display data from latest month to avoid duplicates
                    Abhaas Mohan

                    Thanks Simon!

                     

                    This did the trick.