get the last twelve months, then create a pivot on those dynamic months

Comments

5 comments

  • Konsta Danyliuk

    Hello Sharon.
    May I ask you to provide a sample of the initial data, please, as well as a final result you are looking for. With such information it would be easier to offer suitable solution.

    0
    Comment actions Permalink
  • Sharon

    Hi Konsta and all,
    Sure, here is a sample:

    reporting_month_date is formatted as date format.

    sample:
    reporting_month_date sales
    Jan 2018 293.81
    Mar 2018 109.95
    Sep 2017 500.82
    Jan 2018 5.00
    Apr 2018 250.90
    Apr 2018 100.00
    May 2017 30.60

    The max mm/yyyy is Apr 2018, Therefore using this as one end of the range, the min mm/yyyy would be (maxmm/yyyy-12 months) - Apr 2017.

    The mm/yyyy range is dynamic because it is based on the data - the max mm/yyyy. However, going back 12 months is always static.

    Separately, I will ask about dynamic column headings, but for now, the below would be the end result::

    Apr-17 May-17 Jun-17 Jul-07 Aug-17 Sep-17 Oct-17 Nov-17 Dec-17 Jan-18 Feb-18 Mar-18 Apr-18
    <error> 30.6 <error> <error> <error> 500.82 <error> <error> <error> 298.81 <error> 109.95 350.9

     

    0
    Comment actions Permalink
  • Konsta Danyliuk

    Thank you for additional details, Sharon.

    Let's start from the first part.

    Whether I understood properly that you have a source where records are being appended e.g. on daily basis. You want to have all records for the most recent 12 month, based on reporting_month_date - is this correct?

    0
    Comment actions Permalink
  • Sharon

    Hi Konsta,

    The dataset is not being appended to.  It's one dataset with various dates, but for this pivot, I need to get the max reporting_month_date from the dataset and use that to determine the 12 month date range for the pivot columns.

    Sharon

    0
    Comment actions Permalink
  • Alan Mark

    Hi Sharon,

    Just confirming that the team is following up on your question within the case you submitted for the topic: 16368

    0
    Comment actions Permalink

Please sign in to leave a comment.