Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
Partner
Partner

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 

1 Solution

Accepted Solutions

Re: Set analysis - Count with dynamic condition

Try this

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

View solution in original post

15 Replies
saminea_ANZ
Contributor

Re: Set analysis - Count with dynamic condition

this part is the problem

FirstSortedValue([SNAPSHOT_ID-ID],-DATE,N) 

Because, FirstSortedValue can argument with one sort value on the aggregated based with ranking level. I don't know what is N in your case?

May be use 1,2,3,... instead N and see?

Partner
Partner

Re: Set analysis - Count with dynamic condition

That is exactly what I've done, I replace N by 1 as you can see on the attached picture, but it doesn't work.

The many strange thing is PARAM 1 exactly equal PARAM 2 ..

 

saminea_ANZ
Contributor

Re: Set analysis - Count with dynamic condition

That's where it fails, Perhaps this?

sum(if(DECOMMISSIONED=0,Aggr(Count(if([SNAPSHOT_ID-ID] = Chr(39) & FirstSortedValue([SNAPSHOT_ID-ID],-DATE,1) & Chr(39) AND [TAG_ID-ID]=70,1)),FULL_NAME)))

Partner
Partner

Re: Set analysis - Count with dynamic condition

Thanks for this proposal. I just have tested, but it doesn't work either.

saminea_ANZ
Contributor

Re: Set analysis - Count with dynamic condition

Can you try this alone and let me know what are getting?

Chr(39) & FirstSortedValue([SNAPSHOT_ID-ID],-DATE,1) & Chr(39)

Partner
Partner

Re: Set analysis - Count with dynamic condition

I get the same result than alone, but with single quote.

You can check below the 3rd column is without Chr(39), the fourth is what you asked me to test, and 7th is what you asked me to test before.

Dynamic_count_issue_2.PNG

 

Re: Set analysis - Count with dynamic condition

First things first, instead of using the if statements, why don't you use set analysis here

 

Sum({<DECOMMISSIONED = {0}>} Aggr(
  Count({<[TAG_ID-ID] = {70}>} If([SNAPSHOT_ID-ID] = FirstSortedValue([SNAPSHOT_ID-ID],-DATE,N), 1)), FULL_NAME))

 

Now the issue is that you have an aggregation function within another aggregation function without using the Aggr (FirstSortedValue within Count)... I would try to see if this works

 

Count(DISINCT {<DECOMMISSIONED = {0}, [TAG_ID-ID] = {70}>}
  If([SNAPSHOT_ID-ID] = Aggr(NODISTINCT FirstSortedValue([SNAPSHOT_ID-ID], -DATE, N), FULL_NAME), FULL_NAME)

 

 

Partner
Partner

Re: Set analysis - Count with dynamic condition

Hello Sunny Talkar,

Thanks for your help.

I tried to use set analysis, but I didn't managed to get something right.

I have tried your 2 formulas, but still on issue. You can check it below, the 2 last columns.

Dynamic_count_issue_3.png

 

Re: Set analysis - Count with dynamic condition

Would it be possible to share a qvf file as a sample to test this out?