Qlik Community

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.
anibal_marsis
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

Mensaje editado por: Anibal Martinez-Sistac

7 Replies
ChennaiahNallani
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)

brunobertels
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])

agigliotti
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])

anibal_marsis
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

anibal_marsis
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

anibal_marsis
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

agigliotti
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])