Set Analysis - last AnswerValue for a filtered period per StoreID for same Dimensions

[andre_di]

Hello all,

this is my first post here. So far I have been able to solve any problem with Qlik using the posted topics in the community.
Now I am unfortunately stuck on a topic and am hoping for help from experienced community members 🙂

This is my table:

Project	StoreID	QuestionType	QuestionName	AnswerValue	QuestionDate
Project A	101	A	QuestionName1	yes	25.03.2021
Project A	101	A	QuestionName1	no	15.03.2021
Project A	102	A	QuestionName1	yes	17.03.2021
Project B	101	A	QuestionName1	yes	24.03.2021
Project B	102	B	QuestionName2	yes	05.03.2021
Project B	103	B	QuestionName2	no	31.03.2021

Now I would like to evaluate the questions and their answers. I build the KPI "count(AnswerValue)" and use a pivot table for the visualization.

Time filter is set: entire March

This is the first result:

			KPI (count(Answer))
Project	QuestionType	QuestionName
Project A	A	QuestionName1
		yes	2
		no	1
Project B	A	QuestionName1
		yes	2
		no	0
	B	QuestionName2
		yes	1
		no	1

There is a problem here. In March, for the same question (QuestionName1) with the same project(ProjectA) and the same StoreID (101), there are two entries, two different answers.

-->
I always want to use the last answer (last date) for a filtered period in such cases on StoreId level for the same Projects/StoreIDs/QestionsTyps/QuestionNames. In this case only the answer from 25.03.2021 for the Project A/QuestionType A / QuestionName 1 to get this Pivot-Table in the end:

			KPI ?
Project	QuestionType	QuestionName
Project A	A	QuestionName1
		yes	2
		no	0
Project B	A	QuestionName1
		yes	2
		no	0
	B	QuestionName2
		yes	1
		no	1

I tried lots of different ways with set analysis and it returned always null values.. 

Can someone please help me to solve this problem?

Thanks in advance.

[Quy_Nguyen · Accepted solution]

Hi,

Try this one:  

Count(Aggr((FirstSortedValue(AnswerValue,-QuestionDate)),StoreID, Project, QuestionType, QuestionName))

Tested Result with your data set:

[andre_di]

It worked, thank you very much!