I'm a beginner on Qlik Sense, and I'm stuck with the issue described below, I need to understand.
Let me explain my issue: I'm working on QS to generate reports about SonarQube analysis. We have several projects that are frequently analyzed, and for each project we store issues. I want to count a specific kind of issue ([TAG_ID-ID]=70) for the last analysis ([SNAPSHOT_ID-ID] = LAST_ANALYSIS_ID) by not decommissionned projects (DECOMMISSIONED=0).
So I wrote this formula: FORMULA 1 : sum(if(DECOMMISSIONED=0,Aggr(Count(if([SNAPSHOT_ID-ID] = LAST_ANALYSIS_ID AND [TAG_ID-ID]=70,1)),FULL_NAME)))
This formula works, but now I would like not get the lastest analysis M per project, but have a slicer related to variable N to pick up the M-N last analysis (so a dynamic count depending on the N variable)
And therefore I modify my formula like that :
FORMULA 2 : sum(if(DECOMMISSIONED=0,Aggr(Count(if([SNAPSHOT_ID-ID] = FirstSortedValue([SNAPSHOT_ID-ID],-DATE,N) AND [TAG_ID-ID]=70,1)),FULL_NAME)))
This formula doesn't work, even replacing N by 1 (should have the same result). Yet the only difference is in the first case I use LAST_ANALYSIS_ID calculed in the load script, and in the second one I use the function FirstSortedValue().
Below a picture to illustrate my issue :
First column: Project Name (FULL_NAME field) 2nd column: PARAM 1 - LAST_ANALYSIS_ID filed calculated in the load script as Max([SNAPSHOT_ID-ID]) group by FULL_NAME 3rd column: PARAM 2 - LAST_ANALYSIS_ID calculated with the FirstSortedValue(), same value than PARAM 1 4th column: count using PARAM 1 - it works 5th column: count using PARAM 2 - it doesn't work
Why the FORMULA 2 in the 5th column is not working and how to make it works ? ( still in order to create a dynamic count with the slicer created)
Many thanks by advance for any help you will provide me.