Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
andre_di
Contributor
Contributor

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

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:

ProjectStoreIDQuestionTypeQuestionNameAnswerValueQuestionDate
Project A101AQuestionName1yes25.03.2021
Project A101AQuestionName1no15.03.2021
Project A102AQuestionName1yes17.03.2021
Project B101AQuestionName1yes24.03.2021
Project B102BQuestionName2yes05.03.2021
Project B103BQuestionName2no31.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))
ProjectQuestionTypeQuestionName 
Project AAQuestionName1 
  yes2
  no1
Project BAQuestionName1 
  yes2
  no0
 BQuestionName2 
  yes1
  no1

 

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 ?
ProjectQuestionTypeQuestionName 
Project AAQuestionName1 
  yes2
  no0
Project BAQuestionName1 
  yes2
  no0
 BQuestionName2 
  yes1
  no1

 

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.

Labels (2)
1 Solution

Accepted Solutions
Quy_Nguyen
Specialist
Specialist

Hi,

Try this one:  

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

Tested Result with your data set:

Quy_Nguyen_0-1617859186101.png

 

 

View solution in original post

2 Replies
Quy_Nguyen
Specialist
Specialist

Hi,

Try this one:  

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

Tested Result with your data set:

Quy_Nguyen_0-1617859186101.png

 

 

andre_di
Contributor
Contributor
Author

It worked, thank you very much!