
Re: Count number of ocurrences of metric above certain value in a table
Chennaiah Nallani Mar 8, 2018 6:14 AM (in response to Anibal MartinezSistac)try this
Count({<Operations= {"=Profit > 115"}>} Distinct Operations)
Avg({<Operations= {"=Profit > 115"}>} Operations)

Re: Count number of ocurrences of metric above certain value in a table
Andrea Gigliotti Mar 8, 2018 7:22 AM (in response to Chennaiah Nallani)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])

Re: Count number of ocurrences of metric above certain value in a table
Anibal MartinezSistac Mar 8, 2018 8:55 AM (in response to Andrea Gigliotti )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

Re: Count number of ocurrences of metric above certain value in a table
Andrea Gigliotti Mar 8, 2018 9:22 AM (in response to Anibal MartinezSistac)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])



Re: Count number of ocurrences of metric above certain value in a table
Anibal MartinezSistac Mar 8, 2018 8:35 AM (in response to Chennaiah Nallani)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


Re: Count number of ocurrences of metric above certain value in a table
Bruno Bertels Mar 8, 2018 6:26 AM (in response to Anibal MartinezSistac)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])

Re: Count number of ocurrences of metric above certain value in a table
Anibal MartinezSistac Mar 8, 2018 8:35 AM (in response to Bruno Bertels)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
