Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

daisy1438
New Contributor

error in below set expression?

Hi

I am writing the below set expression.

Count({<[L1 Control Effectiveness]={'Needs Improvement'},[Risk Overall Risk Rating (R)]={'High'}>},[Action Priority]=1 distinct [Issue ID)

before add the [Action Priority]=1 the expression is correct. When add the [Action Priority]=1 expression showing error.

is there any syntax error. Please correct me.

Thanks

7 Replies
arulsettu
Honored Contributor III

Re: error in below set expression?

maybe like this

Count({<[L1 Control Effectiveness]={'Needs Improvement'},[Risk Overall Risk Rating (R)]={'High'}>},[Action Priority]={1} >}distinct [Issue ID])

MVP
MVP

Re: error in below set expression?

It should be this:

Count({<[L1 Control Effectiveness]={'Needs Improvement'},[Risk Overall Risk Rating (R)]={'High'}>},[Action Priority]={1 }>} distinct [Issue ID] )



Red should be removed and green should be added .... so you end up with this:



Count({<[L1 Control Effectiveness]={'Needs Improvement'},[Risk Overall Risk Rating (R)]={'High'},[Action Priority]={1}>} DISTINCT [Issue ID] )


daisy1438
New Contributor

Re: error in below set expression?

Thanks Petter,got it.

daisy1438
New Contributor

Re: error in below set expression?

Hi Peter need one more help in below expression.

Count({<[L1 Control Effectiveness]={'Needs Improvement'},[Risk Overall Risk Rating (R)]={'High'}>} distinct [L1 Control ID]&' '&[Risk ID])

in this above expression I want show only top 5 values. And above expression I am concatenate l1contolid and risk ID is it write that.

my reqirement is caliculate top 5counts based on l1comtrolID and RiskID.

thanks for your help.

MVP
MVP

Re: error in below set expression?

In general you can do a rank selection like this:

If( Rank( Count( Sales ) ) <=5 , Count(Sales) )

So in your case it should be:

If(

Rank( Count({<[L1 Control Effectiveness]={'Needs Improvement'},[Risk Overall Risk Rating (R)]={'High'}>} distinct [L1 Control ID]&' '&[Risk ID]) ) <=5 ,

Count({<[L1 Control Effectiveness]={'Needs Improvement'},[Risk Overall Risk Rating (R)]={'High'}>} distinct [L1 Control ID]&' '&[Risk ID])

)


Also be aware that top 5 could be more than 5 since ranking could be shared if the have the same value for the count.


Using a second parameter with Rank() would allow the expression to still work and select correctly more than 5 but up to ranking number 5:


Rank( ......  , 1 ) <=5


Look at the documention on Rank() to see the options you have...

https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/ChartFunctions/RankingFunctions/ra...

ahmar811
Contributor III

Re: error in below set expression?

try this


Count(distinct {<[L1 Control Effectiveness]={'Needs Improvement'},[Risk Overall Risk Rating (R)]={'High'},[Action Priority]={'1'}>} [Issue ID])

daisy1438
New Contributor

Re: error in below set expression?

Hi Petter I need your help for below requirement.

I have creating one bar chart dimension is region and expression count(RiskID).

MY Table:

date

risk ID

status

region.

above table date filed using that date field I am creating previous date and current date.

I have only one excel data and only one date field.in that date field user select any two dates and compare those two dates one is previous date and other one is current date .what are the risk ID not available in previous date and available in current date than count the Risk ID.

Ex:

01-jan-2018

16-feb-2018

02-mar-2018

10-apr-2018

12-jun-2018 like that.

if user select 16-feb-2018 and 12-jun -2018 compare those two dates it means

16-feb-2018 date Risk ID not available and 12-jun-2018 Risk ID available count the that Risk IDs.

Thanks