Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vonkonormalni
Contributor II
Contributor II

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

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:

                                                                                                                       

YearMonthYearProviderdevicecustomerGuid
2014dec-20142Desktop7757908
2014dec-20142SmartPhone7757908
2014dec-201419Desktop7757908
2014nov-20142Desktop7757908
2014nov-20142SmartPhone7757908
2014nov-201410Mobile (Legacy BI)7757908
2014nov-201410MobileNative7757908
2014nov-201410SmartPhone7757908
2014nov-201419Desktop7757908
2015jan-201511SmartPhone7757908
2015jan-201518Tablet7757908

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

2 Replies
Gysbert_Wassenaar

Perhaps this discussion helps: how to count null records


talk is cheap, supply exceeds demand
vonkonormalni
Contributor II
Contributor II
Author

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.