My data model has three tables and they link on the field ID. Table 2 & 3 are unique on ID while Table 1 is unique on combo of ID and Month Since Start. The field Month Sold Flag is a binary flag for when the product has first sold. I have this populated for every row. Table 2 is populated for every ID. Table 3 only has the State where the sale was completed so it only has IDs that had at least one month sold. So ID = C has never had a sale (it has 0 for Month Sold Flag for all three months) so there is no value that will exist in Table 3.
Table 1
| ID | Month Since Start | Month Sold Flag |
| A | 1 | 0 |
| A | 2 | 1 |
| A | 3 | 0 |
| B | 1 | 0 |
| B | 2 | 0 |
| B | 3 | 1 |
| C | 1 | 0 |
| C | 2 | 0 |
| C | 3 | 0 |
Table 2
| ID | Product Year |
| A | 2015 |
| B | 2016 |
| C | 2018 |
Table 3
I have created a pivot table that has Months Since Start as my row dimension and Product Year and State as my two column dimensions. Product Year is the first dimension. I have created two measures to show cumulative total of the amount of products sold and a cumulative percent among the total number of IDs within that Product Year. I figured a binary flag in Month Sold Flag would make it very easy when doing cumulative views since. i am using the following two formulas.
aggr(RangeSum(Above(SUM([Month Sold Flag]), 0, RowNo())
),[Product Year],State,([Months Since Start], Numeric, asc))
aggr(RangeSum(Above(SUM([Month Sold Flag]), 0, RowNo())
),[Product Year],State,([Months Since Start], Numeric, asc))
/
Count(distinct total <[Product Year]> ID)
Since some IDs do not have a value for State (for example, ID = C) there is a null value shown in the State dimension. This dimension shows 0 for both columns because the sum of Month Sold Flag is 0 for all months which makes sense but obviously this isn't a helpful metric to show as I am looking at cumulative sales by State. If I exclude NULL values from the State dimension then it removes these columns with NULL for State but it removes any IDs in the count() in my denominator of my cumulative % which is not what I want (I want to include the IDs for that product year).
I also tried to exclude zero values in data handling. This fixes the % calc and removes the NULL State values. But I noticed that some of the cells in certain columns are null for the first few Months Since Start. This is because the subset of IDs that correspond to those dimension combinations have 0 for the first few months. Once there is a single ID with 1 for my flag, then the cumulative total and % start to populate.
Please let me know if there is anyway I can make an update to my formula to fix this.
Thanks,
Brian
