Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Help needed with Aggr() function

Hi

I have the following data:

Capture.PNG

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,

1 Solution

Accepted Solutions

Re: Help needed with Aggr() function

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

View solution in original post

11 Replies

Re: Help needed with Aggr() function

May be this:

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

Not applicable

Re: Help needed with Aggr() function

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"

Re: Help needed with Aggr() function

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

Not applicable

Re: Help needed with Aggr() function

Nop, unfortunately still "3":

Capture.PNG

Re: Help needed with Aggr() function

Would you be able to share a sample?

Not applicable

Re: Help needed with Aggr() function

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

Re: Help needed with Aggr() function

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

View solution in original post

effinty2112
Honored Contributor

Re: Help needed with Aggr() function

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
Honored Contributor

Re: Help needed with Aggr() function

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
12008/10/20152
12001/23/20153