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

# 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!

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

What have you tried so far?

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

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.

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

Hi,

Look at the attached app.

hope it helps you.

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

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!

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

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

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

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

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

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

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.

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

Ha! very cute. thanks a lot

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

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

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

Hi,

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

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

Hi,

Unfortunately no. Company information etc.

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

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

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

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

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

thanks!