4 Replies Latest reply on Jul 18, 2018 6:56 AM by Luke Sirakos

    Creating a dimension that is fixed at a row level of detail

    Luke Sirakos

      I am having trouble explaining this in an abstract so I will use an example to walk through what I am trying to do with an accompanying workbook with mock data.

       

      I have monthly data for each customer that looks like the following:

       

      Month
      CustomerCustomer DateCustomer Left DateBalance
      1/1/2017A1/1/2016500
      2/1/2017A1/1/2016750
      ...............
      6/1/2017H5/1/20143/1/20181000
      7/1/2017H5/1/20143/1/20181500
      ...............

       

      Customer date is the date a customer became active, customer left date is the date they became inactive (if applicable)

       

      In my workbook I have two parameters for months in which I want to compare my data and I want to bucket each customer into the following buckets for that time frame:

      - New

      - Left

      - Increased Balance

      - Decreased Balance

      - Maintained Balance

       

      So say my base month is 12/2017 and my comparison month is 6/2017, I want to show a count of customers that became active during those months, became inactive during those months, increased their balance during those months, decreased their balance during those months, or simply maintained their balance during those months.

       

      To do this I feel like I need to create a "Customer Bucket" dimension but make it at the level of detail of each row of data but since the balance figures are dynamic based on the two parameters I am unsure of how to make this work properly. The only solution I can think of is to use the parameter in my custom SQL to extract the balances for those two months in the SQL but the data I am using is rather large and a live connection is slow so I'd like to use an extract and it would be even slower to have to refresh the extract anytime someone wanted to change the dates of interest.