Here is a very basic idea of what I have set up in Tableau so far, but in more of an Excel sort of view.
|Category||Audience||Content||ItemNumber||1st Order Date||1/1/2000||1/2/2000||1/3/2000||1/4/2000|
First, let me explain the above data quickly. The "1st Order Date" column is derived from out SQL database and is pulled in based on a Dimension called "Order Type." Basically it tells me the date the first order was place for a particular item based on the type of order (or who placed the order). We're using this view to pull in this data point from the SQL Server so that we can filter out orders of particular order types that are not applicable to this particular analysis project before the data comes in to Tableau. Using the "1st Order Date" pulled into tableau, we've shifted the timeline of each item's order data so that the appear to all have first been ordered on 1/1/2000, or Day1. We then look at the order data or demand data for the first 6 months. In essence, we are attempting to compare demand data for similar items based on similar "Category," "Audience," and "Content" criteria.
We are also trying to develop a projection, for what demand might look like for a new item that meets this similar criteria. So, we would like to begin by averaging the demand for each day using only the items left after filtering based on the above criteria, and then summing those averages so that we are left with a single number. This is only the first step in developing a projected initial 6-month demand range for new items. I'm just not sure on how to go about doing this without running into the aggregate/non-aggregate calc issue, or how to go about displaying the resulting claculation using tableau. I'd like to use a new sheet, and connect it with the current sheet that i have, so that the calc sheet is based on the filters used in the initial demand display sheet. Thoughts? Questions?