topic Re: Help needed with Aggr() function in QlikView

Help needed with Aggr() function

Thu, 19 Nov 2015 17:25:29 GMT

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,

Re: Help needed with Aggr() function

sunny_talwar — Thu, 19 Nov 2015 18:14:18 GMT

May be this:

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

Re: Help needed with Aggr() function

Thu, 19 Nov 2015 19:23:17 GMT

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

sunny_talwar — Thu, 19 Nov 2015 19:53:34 GMT

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

Re: Help needed with Aggr() function

Thu, 19 Nov 2015 20:41:50 GMT

Nop, unfortunately still "3":

Re: Help needed with Aggr() function

sunny_talwar — Thu, 19 Nov 2015 20:50:00 GMT

Would you be able to share a sample?

Re: Help needed with Aggr() function

Thu, 19 Nov 2015 21:19:10 GMT

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

sunny_talwar — Thu, 19 Nov 2015 21:24:42 GMT

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

Re: Help needed with Aggr() function

effinty2112 — Thu, 19 Nov 2015 21:45:13 GMT

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.

Re: Help needed with Aggr() function

effinty2112 — Thu, 19 Nov 2015 21:56:34 GMT

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

Re: Help needed with Aggr() function

Fri, 20 Nov 2015 14:18:51 GMT

Hi, whenever I have this issue, I just use a simple "Sum(if", e.g.

Sum(if(NPS_Category='Promoter',1,0) and it worked fine for me:

Re: Help needed with Aggr() function

Fri, 20 Nov 2015 14:54:37 GMT

Thanks a lot sunindia‌.... it seems to be working and giving me the number I need. Thanks for you help, now I will try to apply the same logic to fullfil the need we have.

Really appreciate it!!