Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
Showing results for 
Search instead for 
Did you mean: 
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)


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:


2014nov-201410Mobile (Legacy BI)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).

2 Replies

Perhaps this discussion helps: how to count null records

talk is cheap, supply exceeds demand
Contributor II
Contributor II

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.