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

Follow

Sharon

• July 02, 2019 21:50

Hi,

I suppose my question is two-part:

1. How is the best way to determine each month for the last 12 months from a max date in a column?

2. I would like to create a pivot using those dynamically-determined 12 months.  It does not look like GROUPSELECT is a function that can be used dynamically for a pivot. Is the pivot widget the only option for this?

Thank you in advance.

0

• 

  Konsta Danyliuk

  • July 03, 2019 10:52

  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

  • July 03, 2019 15:10

  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

  • July 03, 2019 15:21

  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

  • July 03, 2019 15:29

  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

  • July 03, 2019 17:34

  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.