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

andre_di — Sat, 16 Nov 2024 00:17:23 GMT

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.

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

Quy_Nguyen — Thu, 08 Apr 2021 05:19:55 GMT

Hi,

Try this one:

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

Tested Result with your data set:

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

andre_di — Thu, 08 Apr 2021 09:11:06 GMT

It worked, thank you very much!

topic Set Analysis - last AnswerValue for a filtered period per StoreID for same Dimensions in App Development

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

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

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