New Customer Count and Value using set analysis

[sangeess21]

I'm working on displaying new customer count and value using set analysis. I found few examples on the forum but they do not seem to fit my criteria.

A new customer is one who does not have any activity in the past. Attached is a sample dataset of where I'm getting stuck.

With New SP Customer Count chart, I do get the correct customer count but if I try to reduce the dataset by selecting a year, it shows other years too. E.g.: If I select 2017, it shows 2013,2014,2016 and 2017 but I would like to see 2017 count only.

With New SP Customer Value chart, I would like to see the value when the customer first had activity, but it shows the sum of all values for that customer. E.g.: Customer C under Direct, Service B,Group E has a value of $750.00 in 2013, but it shows $7845.00 (sum of all possible values) in the chart. Any idea where I'm going wrong?

Thanks,

Sangeetha

[sunny_talwar · Accepted solution]

Try these expression

Middle chart

Sum(Aggr(If(Year = Min({<Year>}TOTAL <Customer,[Direct/Indirect Customer],[Service Class],[Service Group]> Year), 1, 0),[Direct/Indirect Customer],[Service Class],[Service Group],Customer, Year))

Bottom Chart

Sum(Aggr(If(Year = Min({<Year>}TOTAL <Customer,[Direct/Indirect Customer],[Service Class],[Service Group]> Year), Sum(Value)),[Direct/Indirect Customer],[Service Class],[Service Group],Customer, Year))

[sangeess21]

Thanks Sunny. This expression works. Can you please explain what the below expression does?

If(Year = Min({<Year>}TOTAL <Customer,[Direct/Indirect Customer],[Service Class],[Service Group]> Year), 1, 0)

[sunny_talwar]

Comparing all years to min year and then display a value if the condition is true

[sangeess21]

Thank you so much