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

[Anonymous]

I have this table of Master Items in Qlik:

Operation ID	Price	Revenue	Profit
1	0.23	3.20	120%
2	0.96	-2.15	200%
3	0.50	1.18	105%
4	0.10	-2.16	116%
...	...	...	...

• 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 Name	Operations	Number 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%
Vynils	3220	...	...	...	...
Memorabilia	1150	...	...	...	...
CDs	430	...	...	...	...
DVDs	810	...	...	...	...
...	...	...	...	...	...

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

[ChennaiahNallani]

try this

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

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

[brunobertels]

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]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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]

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