Distinct across periods with p() and e() and plotting charts

[vonkonormalni]

I have spent some time on this without any progress. I checked the community page, couldn't find the answer, so I  decided to post the question with the complete problem.

What I want is to calculate is distinct actives, reactivated, new actives, retained and churned customers across dimensions (date,

provider, device)

definitions:

1. Actives, this is easy, count(distinct customerid) for the specific period

2. Reactivated, active in past, not active in the previous period, active in the selected period

3. New actives, first ever activity for the selected period, meaning, active now, but not before

4. Retained, active this period, and period before (two periods in a row)

5. Churned, Active previous period, not the current one

I have a row for each customer activity

Information in the model is (in reality it has more info than columns mentioned below):

Activity date (its aggregated on Month level, but the model can be expanded on weekly and daily level)

customerGuid (unique identifier for the customer)

Provider (type of the activity product)

Device (device used)

Example data:

Year	MonthYear	Provider	device	customerGuid
2014	dec-2014	2	Desktop	7757908
2014	dec-2014	2	SmartPhone	7757908
2014	dec-2014	19	Desktop	7757908
2014	nov-2014	2	Desktop	7757908
2014	nov-2014	2	SmartPhone	7757908
2014	nov-2014	10	Mobile (Legacy BI)	7757908
2014	nov-2014	10	MobileNative	7757908
2014	nov-2014	10	SmartPhone	7757908
2014	nov-2014	19	Desktop	7757908
2015	jan-2015	11	SmartPhone	7757908
2015	jan-2015	18	Tablet	7757908

Calculations should work across all dimensions (filtering, clicking)

Example from above, for JANUARY, we have customers

1. 1 (on a customer level), 1 for provider 18, 1 for provider 11, 1 for Smartphone, 1 for Tablet (device level)
2. no reactivation on any level
3. 0 on customer level, 1 on for provider 18 and for provider 11, 1 new activity on tablet
4. 1 (on a customer level), no retention for providers 18,11, 1 retention on Tablet
5. 0 on customer level, churn on Desktop, provider 2 and 19

I can do the calculations in a text box and they work for a specific month selection.

However I can't plot them (chart/pivot with any dimension, especially activity month, and values for each expression)

1. =count( distinct customerGuid)

2. =count( {$<MonthYear = {"$(=Date(MonthYear,'MMM-YYYY'))"}, customerGuid = P({$<MonthYear={"<=$(=Date(addMonths(MonthYear,-2),'MMM-YYYY'))"}>}customerGuid)>-<customerGuid = P({$<MonthYear = {"$(=Date(addMonths(MonthYear,-1),'MMM-YYYY'))"}>}customerGuid)>} distinct customerGuid)

3. =count( {$<MonthYear = {"$(=Date(MonthYear,'MMM-YYYY'))"}, customerGuid = E({$<MonthYear={"<=$(=Date(addMonths(MonthYear,-1),'MMM-YYYY'))"}>}customerGuid)>} distinct (customerGuid))

4. =count( {$<MonthYear = {"$(=Date(MonthYear,'MMM-YYYY'))"}, customerGuid = P({$<MonthYear={"$(=Date(addMonths(MonthYear,-1),'MMM-YYYY'))"}>}customerGuid)>} distinct customerGuid)

5. =count( {$<MonthYear = {"$(=Date(addMonths(MonthYear,-1),'MMM-YYYY'))"}, customerGuid = E({$<MonthYear = {"$(=Date(MonthYear,'MMM-YYYY'))"}>}customerGuid)>} distinct customerGuid)

How can I plot these values in a chart (using the available dimensions)?

Can these calculations be done differently, i.e. by NOT using p() and e()?

Important to note is that I want to do this with expressions on the fly. Calculating these expressions in the script takes time and lots of hard coding (the periods are different, dimensions are different).

[Gysbert_Wassenaar]

Perhaps this discussion helps: how to count null records

[vonkonormalni]

Thank you gwassenaar

I have seen this post before, and helped me a lot to calculate the KPIs

What I can't do, is plot the same values in a chart that contains the date dimension (the one used with the p() and e())

I have the same problem as the example you are mentioning, it only works for a specific month selection.

I really don't know if the approach is good for these requirements.