-
1. Re: Allocating amounts across a date range in Salesforce
Dani Cheng Mar 3, 2017 2:03 PM (in response to Dani Cheng)So what I'm hoping to see is in the example below. What I end up getting is actually something else, though.
This is the dataset: (same structure as an opportunity line item in Salesforce, with start and end date in the same row)
This is what I'm hoping to get:
This is what I actually get:
There are some examples around, most notably these two, but these count records in a date range rather than summing the amounts allocated to each period. I've used both method #2 in the Tableau article and Ćukasz Majewski's sample workbook, but I get different counts. I also don't really understand WINDOW_SUM function having never used it before, but I'd prefer using that as opposed to using a second data source
If anyone can shed some light, it would be very much appreciated. Thank you!
Items Active between Start and End Date
Show Records That Fall Within a Period of Time | Tableau Software
-
2. Re: Allocating amounts across a date range in Salesforce
Jim DehnerMar 4, 2017 6:21 AM (in response to Dani Cheng)
Hi Dani
I will walk you through this but this is the result
I have attached a T10.1 workbook
This is the formula for the difference in months >>DATEDIFF('month',[Start Date],[End Date])+1
This is the calculation for the average >>sum([Cost])/sum([month difference])
Let me know if this helped
Jim
-
Cost across months question.twbx 52.3 KB
-
-
3. Re: Allocating amounts across a date range in Salesforce
Dani Cheng Mar 4, 2017 3:41 PM (in response to Jim Dehner)Hi Jim, this isn't what I'm looking for.
For example, if there's a payment that covers two months, January and February, I want to see that cost as two separate rows, one in January and one in February, with the cost amount equal to the total cost divided by the number of periods. I can only capture the cost in month of the start date, but not any subsequent periods.
Example record:
Start Date; End Date; Cost; Name
Jan 2015; Mar 2015; 30000; Event1
Desired output:
Month; Name; Cost
Jan 2015; Event1; 10000
Feb 2015; Event1; 10000
Mar 2015; Event1; 10000
END
Actual output:
Month; Name; Cost
Jan 2015; Event1; 10000
END
-
4. Re: Allocating amounts across a date range in Salesforce
Deepak Rai Mar 4, 2017 7:11 PM (in response to Jim Dehner)1 of 1 people found this helpfulHi Jim,
I have been following this particular question since beginning and I used another approach as seen in attached. I am stuck towards the end in building some kind of logic to arrive at what he wants. I am now giving up on this. Pl have a look at the Screenshot and attached if you can build it up from here to help him.
Thanks
Deepak
-
5. Re: Allocating amounts across a date range in Salesforce
Jim DehnerMar 5, 2017 7:41 AM (in response to Deepak Rai)
1 of 1 people found this helpfulThanks for looking at this -
I spent some time looking at your work and also trying a couple of different approaches.
I finally concluded that to get the view he wants we would would need a single record in the data base to act like 2, 3, 4 records depending on the date range in the record. (if that makes sense). I don't know any way to do that without restructuring the data source. I looked at scaffolding but that doesn't seem appropriate.
Maybe someone else would know.
Jim
-
6. Re: Allocating amounts across a date range in Salesforce
Deepak Rai Mar 5, 2017 4:55 PM (in response to Dani Cheng)1 of 1 people found this helpfulHi Dani,
This is a real tough nut. I had used a different approach pivoting as well as consulted Jim, so what I could come up finally is that in the following view you are able to access the Service period (From Start to End Month), Actual Month of Service and Cost of Service per Month.
Though I am not able to get in between months, I hope this Helps as well as Jim's Solution. Don't hesitate to mark Helpful to both me and Jim as this was really tough for us to come up to this point too and involved lot of thinking.
Thanks
Deepak
-
7. Re: Allocating amounts across a date range in Salesforce
Deepak Rai Mar 5, 2017 4:56 PM (in response to Dani Cheng)Here is attachment in 10.2
-
Cost Across Months_1.twbx 175.8 KB
-
-
8. Re: Allocating amounts across a date range in Salesforce
Jim DehnerMar 6, 2017 7:35 AM (in response to Deepak Rai)
1 of 1 people found this helpfulThanks Deepak
Very elegant - see what you did -
Jim
-
9. Re: Allocating amounts across a date range in Salesforce
Deepak Rai Mar 6, 2017 9:59 AM (in response to Jim Dehner)Thanks Jim