Help needed with Aggr() function

[#1]

Hi

I have the following data:

And my problem is the following:

As you can see I have a client ID with two closed dates. The issue is that I need to count only the "Promoter" category for August, but the count is giving me the total of 5, instead of 2... how can I fix this?

As you can see in the second chart I have a column that uses Aggr() to get the max closed date, but I don't know to make the column "count" to count only those and ignore the data from January.

Thanks a lot for your help on the same.

Regards,

[sunny_talwar]

May be this:

FirstSortedValue(Aggr(Count(DISTINCT organization_survey_respondent_id), client_id, close_date_cal), -Aggr(Max(close_date_cal), client_id))

[#3]

Thanks a lot for the reply sunindia‌, nevertheless it gave an answer of "6" which remains wrong.... even changing the code a little bit to the following

=FirstSortedValue(Aggr(Count({$<NPS_Category = {'Promoter'}>}DISTINCT organization_survey_respondent_id), client_id, close_date_cal), -Aggr(Max(close_date_cal), client_id))

it gives "3" as an answer when it is supposed to be "2"

[sunny_talwar]

What about this:

=FirstSortedValue({$<NPS_Category = {'Promoter'}>} Aggr(Count({$<NPS_Category = {'Promoter'}>}DISTINCT organization_survey_respondent_id), client_id, close_date_cal), -Aggr(Max({$<NPS_Category = {'Promoter'}>} close_date_cal), client_id))

[#5]

Nop, unfortunately still "3":

[sunny_talwar]

Would you be able to share a sample?

[#7]

yes, sure.. here you have... now, as you look deeper in the example this is when it becomes more complicated:

We need for example to count the Promoters for the whole Fiscal Year..... but you see, for example, client 120 has two events during the year (Jan and Aug)...  and we should count only the most recent one (Aug)... so far so good.... but, if the user select Jan as a Month, we should show the count for that month.... I apologize if it is too complicated. We are hoping that a combination or Aggr and Count can make the trick.

Files attached into the original question.

I really appreciate all your assistance.....

[sunny_talwar · Accepted solution]

This expression seems to work. Can you verify?

=FirstSortedValue({$<NPS_Category = {'Promoter'}>}

Aggr(Count({$<NPS_Category = {'Promoter'}>}DISTINCT organization_survey_respondent_id), client_id, close_date_cal),

-Aggr(Max({$<NPS_Category = {'Promoter'}>} close_date_cal), client_id, close_date_cal))

[effinty2112]

In your Excel file in the field close_date_cal some of the entries are dates and others are timestamps.

Compare lines 442 and 1293 in the sheet.

One has a value of 08/10/2015 the other 08/10/2015  09:20:23.

In the load script try this to load that field

floor(Date(close_date_cal)) as close_date_cal

Count(distinct close_date_cal) was 366 but now becomes 212.

[effinty2112]

I think the count of 5 you had was correct. 2 is correct if you have selected the date of 08/10/2015.

client_id_NPS	close_date_cal	Count({$<NPS_Category = {'Promoter'}>}Distinct organization_survey_respondent_id)
		5
120	08/10/2015	2
120	01/23/2015	3