1 of 1 people found this helpful
You need to be more specific and send and example with a workbook if possible.
What you're asking for looks like a simple window average but an example would help.
WINDOW_AVG(sum([MYMEASURE])) will display 20 if you have the month and your measure in your chart
Thanks for your response. But unfortunately the solution you mentioned will not exactly suit my requirements. My apologies for not explaining the requirements elaborate enough
Let me try to put more details. (Also find attached the excel "Capacity_Demand_graph_Details.xls" where I added more details in seperate tabs)
1. The basic requirement is to create a demand vs capacity graph where capacity will be a bar chart and demand will be a line chart overlapping it (Please refer the tab "Sample Report"). The chart will give an idea about the demand for each month and if company has got the capacity to achieve the demand
2. Capacity data is a pretty straight forward one where we will have different resources and their respective available capacity for each month. Hence its easy to plot that and we have no issues with it (please refer the tab "Capacity" to see how the input data will look like)
3. The problem is with the demand data representation. Here we have different resource requests (or call it demand) over a span of time. The input data will be a specific effort in hours between a start and end date (please refer the tab "Demand" for input data format). Here the requirement is to divide the demand hours among the months between start and end date equally
4. To elaborate let me take an example. Lets take Resource Request 1 in this case. The total hours is 400 and start and end date is 1st Apr to 31-Aug. In "Sample Report" tab if you check the table below the graph you can see how we divided the hours between Apr and Aug in to 5 equal chunks of 80 hours each. Similar calculation must be done for all Resource requests and then it should be used in the graph
With the WINDOW_AVERAGE we can get an average effort between different Resource Requests, but since in this case we need to first divide the total hours among each month between start and end date and then use it in the graph this solution will not help
The real challenge for us is that our input data is coming from Salesforce and hence all the manipulations needs to be done direcly in Tableau. We cannot use any intermediate system to adjust the data (like using a procedure in SQL etc)
Please let me know if you need more details
Thanks in advance,
Thanks for the details, it's a bit more clear.
Can you explain how you're supposed to get the date in yellow in your file ?
Basically you don't want to have your kind of average you also want a controled cartesian product to create the periods in yellow.
Getting the 80 is easy, you need you do a datediff calc in month between start and end date and then divide the total.
But you have another problem here, you said that diff between 1st Apr to 31-Aug is 5 but tehcnically it's 4 with any date function. You need to be more specific on what you expect.
I never used salesforce, so I don't know it's source edition capacity. Can you do sql ?
I've given a solution for this in this thread: Tableau - Monthwise Data Distribution
The posted data is exactly what was posted in that thread as far as I can tell, as is the question. Please don't do that as it can end up wasting our effort. (And in fact, your description of the data and problem would have been useful in helping me arrive at the solution that I did faster).
Thanks Jonathan !
We were able to fix the issue related to monthly data split by using a separate excel data source having different combinations of months/years. We used this excel as primary and blended the other two SFDC data sources and achieved the month wise split as expected
The new issue we have is related to the filters where we are unable to filter using fields other than blended fields. I saw your response in another thread where you mentioned that Tableau currently allows filtering dimensions from primary data source only in cases where we use multiple data sources.
We are looking at other alternatives there and will let you know if we face more challenges.
However we are at least able to create a report with month wise split now. Appreciate your detailed informative feedbacks!
The blend solution to the filtering problem is to increase the granularity of the scaffold source so it includes the filtering dimensions as well, then you can add them as linking dimensions so the filters will effectively be applied to the second source (via the primary source).