# New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Contributor

## New Customer Count and Value using set analysis

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

Tags (2)
1 Solution

Accepted Solutions
MVP

## Re: New Customer Count and Value using set analysis

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

4 Replies
MVP

## Re: New Customer Count and Value using set analysis

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

Contributor

## Re: New Customer Count and Value using set analysis

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)

MVP

## Re: New Customer Count and Value using set analysis

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

Contributor

## Re: New Customer Count and Value using set analysis

Thank you so much