Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
sangeess21
Creator
Creator

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
sunny_talwar

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

View solution in original post

4 Replies
sunny_talwar

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
Creator
Creator
Author

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
Creator
Creator
Author

Thank you so much