Getting accurate count of distinct ID's across multiple months

[tschullo]

I have a Text object that displays the total onboarded customers basd on selected months.

Because I have multiple date fields to match against the user selected months, I have a date island with the distinct set of dates in all date columns.

My expression is:

Count({<StartMonthYr={$(vSelectedDates)}>} distinct CUST_ID)

where vSelectedDates=Concat(DISTINCT Chr(39)&MonYr&Chr(39),',')

I use distinct here becasue my model allows for multiple records per ID for a given date.

The issue is that when I select two months, I am getting the DISTINCT number of clients that were onboarded during both months.

If the months are a year apart, it is possible that the same customer was onboarded twice, but I am only counting him once becasue of distinct.  Example: Jan 2014 onboarded = 100,  Jan 2015 onboarded = 55. If 5 of those customers were onboarded in both months, my text oject will currently display the number of onboarded for the selected months as 150. It needs to be 155.

I though using aggr was the way to go, but have not seen an example that fits my requirements.

Any ideas?

Thanks,

Tony

[MK_QSL]

=SUM(Aggr(Count({<StartMonthYr={$(vSelectedDates)}>} distinct CUST_ID),SartMonth))

[maxgro]

you need to sum the aggr of your expr by StartMonth

[tschullo]

That did not work, I got zero.

I used:

=SUM(Aggr(Count({<StartMonthYr={$(vSelectedDates)}>} distinct CUST_ID),StartMonthYr))

I'm guessing that is what you meant

[MK_QSL]

What is the format of StartMoth?

Can you provide  sample data or sample app?

[tschullo]

I attached a QVW that illustrates the problem.

[MK_QSL]

What is the reason behind using ICELAND table?

[tschullo]

Island table is because users wants to pick a month and then report on which customers are onboarded (REC_START_DT falls within that month) and at the same time report exiting customers (REC_END_DT falls within that month)

I don't like the idea, but I don't know a better way to meet the requirement...

[MK_QSL]

Understood but particularly for this example there is no need to use ICELAND Month and Year, as you haven't used StartDate and EndDate. We can directly use the Month and Year generated from StartDate or EndDate.

[tschullo]

Right, but the example is just a piece of the dashboard.  Given those constraints (i.e. island dashboard) is there a way to accomplish what I need to do?