1 Reply Latest reply: Mar 6, 2015 10:52 AM by Oleg Troyansky

# Churn users using set analysis

Hi,

I am trying to calculate the churn user of a given date using set analysis. The definition of churn user here being, user who were active during x-8 to x-14 date range, but not active durinf x to x-7 date range. x being the date is question. I have build the following expression which seems to give correct result if i use it in a text box. But this doesnt work in a chart with 'date' as dimension. This is because the max(date) get computed in global level and does not consider the 'date' dimension. Does anyone know how to overcome this?

=count( {\$<user = P({\$<date={">=\$(=date(max(date)-14))"}>} user) - P({\$<date={">=\$(=date(max(date)-7))"}>} user)>}  distinct user)

• ###### Re: Churn users using set analysis

you understood correctly why it doesn't work - Set Analysis cannot be sensitive to the dimensional value. The way to overcome this problem is to build the "as of date" table and  to calculate the corresponding flag(s) there. There is a number of documents describing how to build the "as of date" table, including my blog:

QlikView Blog Q-Tip #4 How to Use "As of Date" table | Natural Synergies

cheers,

Oleg Troyansky