topic Re: Comparing customers by time since signup in QlikView

Comparing customers by time since signup

Wed, 27 Apr 2016 05:09:30 GMT

Hi all,

I've been trying to create a kind of cohort analysis where I try to analyse how customers develop from their signup time. I have the following information for every day and customer:

Date, CustomerID, Documents

For each CustomerID I know their FirstSeenDate ( gathered during load by doing a min(Date) group by CustomerID ) and I have it stored in a separate table.

I've now created a chart table with CustomerID as the dimension and three expressions:

Growth during first 4 weeks sum({<Date = {$>=FirstSeenDate $<(FirstSeenDate+27)}>} Documents)

Growth during second 4 weeks sum({<Date = {$>=FirstSeenDate+27 $<(FirstSeenDate+54)}>} Documents)

Growth during third 4 weeks sum({<Date = {$>=FirstSeenDate+54 $<(FirstSeenDate+81)}>} Documents)

Simply put, this doesn't work. I end up with 0 in the columns and I cannot understand why.

Thanks for any ideas.

John

Re: Comparing customers by time since signup

sunny_talwar — Wed, 27 Apr 2016 15:52:13 GMT

Syntax doesn't seem right, can you try these:

sum({<Date = {">=$(=FirstSeenDate)<$(=FirstSeenDate+27)"}>} Documents)

sum({<Date = {">=$(=FirstSeenDate+27)<$(=FirstSeenDate+54)"}>} Documents)

sum({<Date = {">=$(=FirstSeenDate+54)<$(=FirstSeenDate+81)"}>} Documents)

Re: Comparing customers by time since signup

Thu, 28 Apr 2016 03:44:33 GMT

Unfortunately, this doesn't help.

I'm attaching a qvw that demonstrates the problem together with a possible solution I've come up with using a calculated dimension of =Date - FirstSeenDate

Re: Comparing customers by time since signup

sunny_talwar — Thu, 28 Apr 2016 09:52:05 GMT

The way your data is structured, data coming from different tables, set analysis is not going to work. Try these:

1) Sum(If(Date >= FirstSeenDate and Date < FirstSeenDate + 27, Documents))

2) Sum(If(Date >= FirstSeenDate + 27 and Date < FirstSeenDate + 54, Documents))

3) Sum(If(Date >= FirstSeenDate + 54 and Date < FirstSeenDate + 81, Documents))

Re: Comparing customers by time since signup

Fri, 29 Apr 2016 06:42:35 GMT

Thanks, this explains my problems and solves them at the same time