Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
sangeess21
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

1 Solution

Accepted Solutions

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

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

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

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

sangeess21
Contributor

Re: New Customer Count and Value using set analysis

Thank you so much