# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
New Contributor III

## Count number of ocurrences of metric above certain value in a table

I have this table of Master Items in Qlik:

Operation IDPriceRevenueProfit
10.233.20120%
20.96-2.15200%
30.501.18105%
40.10-2.16116%
............

• I have created a new table with 1 Master item: "Product Name" and a calculated field "Operations", whose formula is: num(Count(DISTINCT [Operation id]),'#,##0',',' )
• The attribute "Profit" is as well a calculated metric, whose formula is: num((Sum(Win) / Sum(Pay))*100,'0.00') &'%' where Win and Pay are in fact master items.

. What I would like to do is to add 4 desired metrics, which are:

1. Number of operations with Profit > 115%
2. Percentage of operations with Profit > 115%
3. Number of operations with Profit between 100% and 105%
4. Percentage of operations with Profit between 100% and 105%

The look of my desired table would be something like this:

Product NameOperationsNumber of Operations with Profit > 115%Percentage of Operations with Profit > 105% Number of Operations with Profit between 100% and 105% Percentage of Operations with Profit between 100% and 105%
Vynils3220............
Memorabilia1150............
CDs430............
DVDs810............
..................

I thank before-hand for any help on the expression or Qlik set analysis expression required to achieve my desired table. Anibal

7 Replies
Contributor III

## Re: Count number of ocurrences of metric above certain value in a table

try this

Count({<Operations= {"=Profit > 115"}>} Distinct Operations)

Avg({<Operations= {"=Profit > 115"}>} Operations)

Valued Contributor II

## Re: Count number of ocurrences of metric above certain value in a table

Hi

Try this

Number of operations with Profit > 115%

=Count({\$<Profit={">\$(=105%)"}>}[Operation])

Percentage of operations with Profit > 115%

=Count({\$<Profit={">\$(=105%)"}>}[Operation])

/

Count([Operations])

Number of operations with Profit between 100% and 105%

Count({\$<Profit={">=\$(=100%)<=\$(=105%)"}>}[Operations])

Percentage of operations with Profit between 100% and 105%

Count({\$<Profit={">\$(=100%)<\$(=105%)"}>}[Operations])

/

Count([Operations])

Honored Contributor II

## Re: Count number of ocurrences of metric above certain value in a table

maybe this:

try this

1- Count( {< [Operation ID] = {"=Sum(Profit) > 1.15"} >} Distinct [Operation ID] )

2- Count( {< [Operation ID] ={"=Sum(Profit) > 1.15"} >} Distinct [Operation ID] )

/

Count( total [Operation ID])

3- Count( {< [Operation ID] = {"=Sum(Profit) >= 1 and Sum(Profit) <= 1.05"} >} Distinct [Operation ID] )

4-  Count( {< [Operation ID] ={"=Sum(Profit) >= 1 and Sum(Profit) <= 1.05"} >} Distinct  [Operation ID] )

/

Count( total [Operation ID])

New Contributor III

## Re: Count number of ocurrences of metric above certain value in a table

Hi Chennaiah,

I am not available to achieve the "Percentage of operations with Profit > 115%". After checking my attributes I have realized that:

• My "Operations" metric in the second table is calculated as: num(Count(DISTINCT [Operation id]),'#,##0',',' )
• My Profit metric is calculated as: num((Sum(Win) / Sum(Pay))*100,'0.00') &'%' where Win and Pay are in fact master items.

I am editing my question as Operations is not a master item but a calculated metric.

Anibal

New Contributor III

## Re: Count number of ocurrences of metric above certain value in a table

Hi Chennaiah,

I am not available to achieve the "Percentage of operations with Profit > 115%". After checking my attributes I have realized that:

• My "Operations" metric in the second table is calculated as: num(Count(DISTINCT [Operation id]),'#,##0',',' )
• My Profit metric is calculated as: num((Sum(Win) / Sum(Pay))*100,'0.00') &'%' where Win and Pay are in fact master items.

I am editing my question as Operations is not a master item but a calculated metric. Thanks in advance for your help.

Anibal

New Contributor III

## Re: Count number of ocurrences of metric above certain value in a table

Hi Andrea,

I Edited the question as Profit and Operations are not master items but calculated fields. This is giving me some errors in Qlik. Operations is calculated as: num(Count(DISTINCT [Operation id]),'#,##0',',' ) and Profit as: num((Sum(Win) / Sum(Pay))*100,'0.00') &'%'

Sure this is changing substantially Qlik expressions for the answers. Thanks in advance for your help, Anibal

Honored Contributor II

## Re: Count number of ocurrences of metric above certain value in a table

Hi Anibal,

try change as below:

try this

1- Count( {< [Operation id] = {"=((Sum(Win) / Sum(Pay)) > 1.15"} >} Distinct [Operation id] )

2- Count( {< [Operation id] ={"=((Sum(Win) / Sum(Pay)) > 1.15"} >} Distinct [Operation id] )

/

Count( total [Operation id])

3- Count( {< [Operation id] = {"=((Sum(Win) / Sum(Pay)) >= 1 and ((Sum(Win) / Sum(Pay)) <= 1.05"} >} Distinct [Operation id] )

4-  Count( {< [Operation id] ={"=((Sum(Win) / Sum(Pay)) >= 1 and ((Sum(Win) / Sum(Pay)) <= 1.05"} >} Distinct  [Operation id] )

/

Count( total [Operation id])

Community Browser