Get new customers KPI in straight table

[g_collet]

Hi,

I'm trying to calculate the number of new customers for each year in  a straight table.

The straight table contains :

     - Dimension : Year

     - Expression :

          * Last Year : Above(Year)

          * New Customers : Count(Customer ID = E(Year={$(=Above(Year))}) Distinct Customer ID)

          * New Customers 2017 : Count(Customer ID = E(Year={"2016"}) Distinct Customer ID)

A new customer in a year is a customer without sales fact in the year - 1.

But the expression for New Customers does not work fine. It returns the distinct number o customers for each years.

If i use this expression : Count(Customer ID = E(Year={"2016"}) Distinct Customer ID), i obtain the correct number of new customers in 2017.

Any idea of the correct method to calcutate this KPI?

Thanks,

[sunny_talwar]

May be something like this:

Sum(Aggr(If(RowNo() = 1, 1, 0), CustomerID, Year)

Here I am assuming that Year's load order is Ascending.... if it isn't then you can use The sortable Aggr function is finally here!‌ to resolve this (if you have QV12) or solve the sort order in the script to make sure Year is sorted correctly.