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!

[#2]

Ha! very cute. thanks a lot

[#3]

Hi,

I was wondering if you could help me with this strange phenomenon:

The expression seems to work OK with an explicit (i.e. daily) date field,

but I also have a field based on that date field that simply takes the year and month for convenience.

As you can see, if I select Oct-13 than only dates in the month of october are possible in the explicit field.

Also, the blue square shows the =vDate value, which seems to be correct as well for our needs.

However, for some reason, the result is 0 when I got through month-year selection.

I find it strange. Any insights?

Thanks

Al

[#4]

Hi,

Can you send your application (or part of it) ?

It would be easier to help you.

[#5]

Hi,

Unfortunately no. Company information etc.

[#6]

I added a more elaborate question with dummy data here:

http://community.qlik.com/message/407133#407133

but it appears the question wasn't approved just yet