Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi,
Try this one:
Count(Aggr((FirstSortedValue(AnswerValue,-QuestionDate)),StoreID, Project, QuestionType, QuestionName))
Tested Result with your data set:
Hi,
Try this one:
Count(Aggr((FirstSortedValue(AnswerValue,-QuestionDate)),StoreID, Project, QuestionType, QuestionName))
Tested Result with your data set:
It worked, thank you very much!