topic Re: New Customer Count and Value using set analysis in QlikView

New Customer Count and Value using set analysis

sangeess21 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: New Customer Count and Value using set analysis

sunny_talwar — Wed, 10 Jan 2018 16:55:36 GMT

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

Re: New Customer Count and Value using set analysis

sangeess21 — Wed, 10 Jan 2018 18:49:02 GMT

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

sunny_talwar — Wed, 10 Jan 2018 18:51:48 GMT

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

Re: New Customer Count and Value using set analysis

sangeess21 — Wed, 10 Jan 2018 19:22:14 GMT

Thank you so much