Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
sangeess21
Creator
Creator

Set analysis help

Hi,

I’m trying to show unique customers per quarter for the last 5 years. The expression works well when no year selection is made. But when year is selected, it hides few customers and the total count varies.

E.g.: Customer “AIEAJKI” is a unique customer in 2015 (Direct/Product) and 2017 (Direct/Services). The report displays it when no year selection is made. But when I select 2015, it disappears from the report. Also, I would like the chart to show data only for the last five years. When I select 2013 or prior years, it shouldn’t display any data. Why does that happen? Maybe, I’m missing something in my expression. Attached is my sample dataset. Any help is appreciated.

Here is my expression:

Sum(Aggr(If(Customer.Mth_Year = Min({1<Date={">=$(=YearStart(Max(Date), -4))<=$(=Date(Max(Date)))"},Customer.QTR=,Customer.Month=,Customer.Year>}TOTAL <[Direct/Indirect Customer],Segment,[Direct/Indirect Customer Name]> Customer.Mth_Year), 1, 0), Direct/Indirect Customer],Segment,[Direct/Indirect Customer Name], Customer.Mth_Year))

Thanks!

Labels (2)
1 Solution

Accepted Solutions
sunny_talwar

May be try this

Sum(Aggr(If(Customer.Mth_Year = Min({1<Date={">=$(=YearStart(Max({1}Date), -4))<=$(=Date(Max({1}Date)))"}, Customer.QTR, Customer.Month, Customer.Year>}
	TOTAL <[Direct/Indirect Customer], Segment, [Direct/Indirect Customer Name]> Customer.Mth_Year), 1, 0),
		[Direct/Indirect Customer],Segment,[Direct/Indirect Customer Name], Customer.Mth_Year))

View solution in original post

2 Replies
sunny_talwar

May be try this

Sum(Aggr(If(Customer.Mth_Year = Min({1<Date={">=$(=YearStart(Max({1}Date), -4))<=$(=Date(Max({1}Date)))"}, Customer.QTR, Customer.Month, Customer.Year>}
	TOTAL <[Direct/Indirect Customer], Segment, [Direct/Indirect Customer Name]> Customer.Mth_Year), 1, 0),
		[Direct/Indirect Customer],Segment,[Direct/Indirect Customer Name], Customer.Mth_Year))

View solution in original post

sangeess21
Creator
Creator
Author

Thanks Sunny. It's working as expected 🙂