Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.