14 Replies Latest reply: Oct 16, 2013 8:49 AM by Al On RSS

    How to sum a field from the beginning of time until the start of current selected period?

      Hi all,

       

      I'll use an example to explain my question and what I'm trying to achieve:

       

      Let's say I have a subscription business (like cables, newspaper...) that's been operating for 10 years and I have a table with the names and customer IDs of all customers I ever got, including the date when they became customers.

       

      What I want to do is to be able to sum up and see the total number of customers I had since the beginning of time (when the business started selling...) until any certain point. So:

       

      If the business started in Jan 1 2000, and now we're in Oct 2013, I'd like to see the total number of customers that joined since Jan 1 2000 and up to the end of September 2013. (another way to look at it is to see how many customers I have at the very beginning of Oct 2013. Assuming no customers ever left me for simplicity...)

       

      It gets trickier.

      First, I'd like to have some control on the time units, that is I have a time cycle group for weeks-months-quarters. So, if i select quarters I'll see how many customers I had by the end of the previous quarter (or right at the start of the current quarter), and if I select months I'll see all the customers by the end of the previous month.

       

      Second, i'd like to "go back in time", that is, for example, select Q2 2010 and see how many customers I had by the end of Q1 2010.

       

      So...... hopefully I made myself clear and you could help me!

       

      many thanks!