3 Replies Latest reply on Oct 11, 2018 10:56 AM by Ken Linder

    Dynamic Set from Another Dimension

    ryan.spjut
    ryan.spjut Oct 6, 2018 9:13 PM

      Hi,

       

      Using Version 2018 2.2.

       

      My data source is customer data with the list of customers and the corresponding month that the customer transacted business.  I'm trying to track all of the monthly revenue amounts (using a line graph) for customers that existed in a single month chosen by the user.

       

      Example:  Here is sample data.

       

      Customer Name
      		Year and Month that Customer Transacted Business
      		Total Revenue in the Month
      ABC Company2017-01$100
      Acme Products2017-01$489
      Balloons Inc.2017-01$932
      ABC Company2017-02$700
      Acme Products2017-02$283
      Balloons Inc.2017-02$892
      New Corp2017-02$767
      ABC Company2017-03$200
      Acme Products2017-03$552
      New Corp2017-03$850

       

       

      Note that ABC Company and Acme Products were customers in Jan, Feb and Mar of 2017.  New Company came on for Feb and Mar.  Balloons Inc had no business in Mar, only Jan and Feb.

       

      I'm displaying the monthly revenue as a line graph, and essentially I'm trying to track revenue trends for "same customer cohort" determined by the month I choose.

       

      So I'd like a filter where I can choose "2017-01" and see the revenue trends for ABC Company, Acme Products and Balloons Inc. since they were all customers in 2017-01, including their revenue from 2017-02 and 2017-03.  Naturally, New Corp would be ignored with this filter selection since it had no revenue in 2017-01.

       

      I've explored several threads and may have come across the answer already without knowing it.  I've considered combining sets but that requires two original sets from the same dimension and I'm trying to combine sets from two different dimensions (Customer Name and Year).  I've considered filtering on two dimensions but that obviously limits the data to only the filtered month.  I've looked at parameters as a formula condition but it doesn't seem to work like that.  Context filters might be the answer but I'm not familiar enough with that, especially when the relevant values apply to the filter and not the full data set.  Maybe there's another way to create a dynamic set of Customer Names -- based on a Year-Month selection -- and use that Customer Name set as a filter.

       

      Thanks for your help.

      • 91 Views
      • Categories: Actions & Filters
      • Tags:
        • 1. Re: Dynamic Set from Another Dimension
          Mahfooj Khan
          Mahfooj Khan Oct 7, 2018 1:38 AM (in response to ryan.spjut)

          I don't know how much I've understood your question,

          Do you want something like this? Each line represents a company and over months their revenue. For this viz you don't need to create any set. Drag your fields in canvas as per below screenshot.

          Let me know if this help. Workbook attached for your reference.

           

          Mahfooj

          • 2. Re: Dynamic Set from Another Dimension
            ryan.spjut
            ryan.spjut Oct 7, 2018 9:55 AM (in response to Mahfooj Khan)

            Thanks for your input.  I'm adding some additional clarity to my question.

             

            My intention is to only see ABC, Acme and Balloon if I make some kind of filter selection for January.  New Corp would not be included at all.

             

            If I make a filter selection for February, all companies show up (with their full revenue history) since everyone had activity in February.

             

            Likewise, if I select for March, only ABC, Acme and New Corp would show.  The red line for Balloon would not be on the visualization since they had no activity in the month I selected.

             

            I've attached the resulting visualizations I'm hoping to achieve (labeled JanOnly, FebOnly and MarOnly).  These were accomplished by manually excluding the customers that didn't have activity in the chosen month.  I'm hoping to find a filter option that will allow me to just choose "2017-01" to get the JanOnly result, and so on.

             

            Thanks again.

             

            JanOnly.png

             

            FebOnly.png

             

            MarOnly.png

            • 3. Re: Dynamic Set from Another Dimension
              Ken Linder
              Ken Linder Employee Oct 11, 2018 10:56 AM (in response to ryan.spjut)

              So I've worked with this a little bit and may have a solution. I'm attaching a workbook that functions as below :

               

              • The first instance of a revenue per customer name is generated by the calculated field "First Date"
              • The last instance of revenue recorded is found by "Last Date".
              • The Parameter "Month Parameter" allows for selection of the month. It currently only has three values, since there were only three months in the provided sample.
              • The calculated field "Month Select" assigns a date to each value in the parameter. In this instance, it assigns 1 to 01-01-2017, 2 to 02-01-2017, etc. This can be expanded easily, and should just match the values in the "Month Parameter".
              • Finally, the view is filtered by a value "In Range", which checks if the date being selected by "Month Select" is between the "First Date" value and the "Last Date" value, and only shows companies for which that is true.

               

              This solution looks like it accomplishes the goal, presuming that once a company stops generating revenue, it does not start again. If, for instance, "Balloons Inc." started generating revenue again in April, then it would show up for all selections between January and April, and would just show a gap for the month of March.