Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
QS_kdefok
Partner - Contributor III
Partner - Contributor III

Set analysis - Count with dynamic condition

Hello everyone,

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 :

Dynamic_count_issue.PNG

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.

Regards,

kdefok 

Labels (5)
15 Replies
QS_kdefok
Partner - Contributor III
Partner - Contributor III
Author

I'm going to build it because I can't share my qvf as it's based on company data.

I'll post it here as soon as I manage to reproduce the same behavior.

Regards,

kdefok

sunny_talwar

It would be difficult to know the issue without seeing it, but in general the problem with the expression which uses the firstsortedvalue instead of the field is that it is using FirstSortedValue within Count.... you are not allowed to do things like

 

Sum(Count(Measure))... you need to use Aggr... Sum(Aggr(Count(Measure), SomeDimension/s))

QS_kdefok
Partner - Contributor III
Partner - Contributor III
Author

I come back with a QVF file, where you can find the same structure than before.

I draw too charts :

- a table as before
TEST_table.PNG

 - a pivot table where projects are group by programs (the final purpose)

TEST_Ptable.PNG

 

And I created a slider on the N variable, but it seems not working so I haven't use it for now.

I could attach you also the excel file from which I loaded datas if you need it.

Thanks by advance for your help.

Regards,

kdefok

 

 

sunny_talwar

Try this

Count(DISTINCT {<[TAG_ID-ID] = {70}, DECOMMISSIONED = {0}>} If([DATE] = Aggr(NODISTINCT Max(DATE, N), FULL_NAME, PROGRAM), [ISSUES_ID-ID]))
QS_kdefok
Partner - Contributor III
Partner - Contributor III
Author

Great job, it works ! Thanks a lot 🙂

Just for my mind, I don't understand why you used NODISTINCT aggr rather than DISTINCT in your second part.
Basicaly, I would choose DISTINCT but it seems not be the good choice)