
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Help needed with Aggr() function
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,
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be this:
FirstSortedValue(Aggr(Count(DISTINCT organization_survey_respondent_id), client_id, close_date_cal), -Aggr(Max(close_date_cal), client_id))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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"

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nop, unfortunately still "3":

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Would you be able to share a sample?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.....

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 |

- « Previous Replies
-
- 1
- 2
- Next Replies »