Dynamic Set from Another Dimension
ryan.spjut Oct 6, 2018 9:13 PMHi,
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 Company | 2017-01 | $100 |
Acme Products | 2017-01 | $489 |
Balloons Inc. | 2017-01 | $932 |
ABC Company | 2017-02 | $700 |
Acme Products | 2017-02 | $283 |
Balloons Inc. | 2017-02 | $892 |
New Corp | 2017-02 | $767 |
ABC Company | 2017-03 | $200 |
Acme Products | 2017-03 | $552 |
New Corp | 2017-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.