Comparing customers by time since signup

[#1]

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

[sunny_talwar]

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)

[#3]

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

[sunny_talwar · Accepted solution]

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))

[#5]

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