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

[#1]

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!

[ThornOfCrowns]

What have you tried so far?

[#3]

not too much. i'm rather a newbie... I thought about maybe using above but figured it won't give me the data from the beginning of time.

i thought about set analysis but can't think of a way to use the end of the previous period as a stop point.

[#4]

Hi,

Look at the attached app.

hope it helps you.

[maksim_senin]

Hi,

Is not it more easy to count your customers by selecting a period e.g. from the day one to the day you need?

In case you still see the idea unuseful I assume you should take the first day of the selected period minus 1 day - e.g. Min(Date)-1, store it into a variable (say DateTo), then count your customers applying a set analysis similar to something like this - count({1<Date={">=2000-01-01<=$(DateTo)"}>} CustId).

Not sure whether it's correct syntactically, but the main points are:

1. the "1" is mandatory between { and < since it says to ignore current selections (and here you can face other problems actually)

2. you need to build correct string expression in order to describe you period in set analysis using >= and <= operatiors

Hope this helps.

Best regards,

Maxim

[#6]

Hi,

wow,

this is amazing.

One thing though - the month selected in your file is included in the count. I wanted it to include up until the month selected (not including).

Can that be done?

Thanks a lot!

[#7]

Ok, so modify the variable vDate  with the formula =Monthstart(max(Date))

and in the Set Analysis change the '<=' in '<'

[#8]

don't want to be an awful person but now it doesn't work with quarters

[#9]

thanks!

[#10 · Accepted solution]

You can have a different function when a quarter is selected :

= If GetSelectedCount(Quarter)=1,

QuarterStart(max(Date)),

MonthStart(max(Date))

)

But if you also have to work with half-year and other time dimensions, you will have to use different functions for each.