aggr and dates

orh12345 — Mon, 09 Nov 2020 10:14:50 GMT

Hi,
New in a click
I have a table that contains IDs and dates and response.
Counting is done using the hierarchy of the strongest reaction. Positive (1), negative (2), no answer (3).
I want to count in the table the amount of people who responded positive, negative and unanswered.
for example:https://community.qlik.com/t5/New-to-QlikView/count-strongest-line/m-p/1741515#M392664

The formula I use: count ({<Type =>} distinct if (Aggr (min ({<Type =>} total <Cm_Lead_Key, Emp_Id> Strong_Response_Score), Emp_Id, Cm_Lead_Key) = 1, Cm_Lead_Key))

Each month individually is counted well, but that I add horizontal dates there is a problem.
When I count January alone I have 112 records. As soon as I add February everyone who responded negatively in January and positive in February will be told positively in January that the first reaction was in January.
How can I overcome this?

dates, campaigns ,responses
02.01.2020 , 1, Negative
03.02.2020, 1, Positive

In this example if I select January I want 1 to appear negative
But if I choose January and February together I want it to appear that in February there is 1 positive and in January 0
(Not to mention even in the negative)

Thank you

Re: aggr and dates

rubenmarin — Mon, 09 Nov 2020 11:52:54 GMT

Hi, I think you need to add date field to aggr and total, if you don't add this you are telling Qv to deliberate ignore dates and set the min value it founds:

count ({<Type =>} distinct if (Aggr (min ({<Type =>} total <Cm_Lead_Key, Emp_Id, Date_field> Strong_Response_Score), Emp_Id, Cm_Lead_Key) = 1, Cm_Lead_Key,Date_field))

Maybe you only need to add it on the total clause.

topic aggr and dates in QlikView

aggr and dates

Re: aggr and dates