edit: scratch previous, makes sense.
Can you attach the Excel workbook you used in screenshot as well? I'm having a hard time figuring out how you've calculated Lost Customers even on that view.
1 of 1 people found this helpful
Lost customers is the tricky bit because by definition, there is no data to partition, so you must use the previous month's data with a window function to calculate it. This means a few things:
1) date filters will be tricky to get to work correctly, if you want to limit the view any
2) You will not be able to (with how this workbook is put together) be able to identify specific customers who are lost, it only works in aggregate
Thanks Justin, I ended up using Custom SQL and joined to the table a second time for the previous month. Your response was very helpful and correct in most ways, but the SQL will probably be easier to modify when I have completely forgotten about this workbook and am asked in a year to make modifications! I'll also need the ability to filter and aggregate a grand total for all fields, so again custom sql is much easier.
Custom SQL definitely works magic a lot of the time. I rarely suggest it on the forums because it’s not usually where the skillsets are with typical users, but it’s OFTEN my personal solution.
Principal Business Intelligence Analyst
2055 NW Savier St
Portland, OR 97209 USA
O: +1 503-450-5759