Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
orh12345
Contributor
Contributor

aggr and dates

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

1 Reply
rubenmarin

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.