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

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

Tue, 15 Oct 2013 12:14:33 GMT

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?

ThornOfCrowns — Tue, 15 Oct 2013 12:20:03 GMT

What have you tried so far?

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

Tue, 15 Oct 2013 12:32:21 GMT

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?

Tue, 15 Oct 2013 12:33:54 GMT

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?

maksim_senin — Tue, 15 Oct 2013 12:42:28 GMT

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?

Tue, 15 Oct 2013 13:06:15 GMT

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?

Tue, 15 Oct 2013 13:13:19 GMT

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?

Tue, 15 Oct 2013 13:17:10 GMT

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?

Tue, 15 Oct 2013 13:17:19 GMT

thanks!

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

Tue, 15 Oct 2013 13:25:39 GMT

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?

Tue, 15 Oct 2013 13:46:43 GMT

Ha! very cute. thanks a lot

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

Wed, 16 Oct 2013 06:57:18 GMT

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

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

Wed, 16 Oct 2013 11:27:47 GMT

Hi,

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

It would be easier to help you.

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

Wed, 16 Oct 2013 12:46:20 GMT

Hi,

Unfortunately no. Company information etc.

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

Wed, 16 Oct 2013 12:49:42 GMT

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