Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Showing results for

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements

Qlik Cloud Maintenance is scheduled between March 27-30. **Visit Qlik Cloud Status page for more details.**

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Sense
- :
- Count number of ocurrences of metric above certain...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Showing results for

anibal_marsis

Contributor III

2018-03-08
05:50 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

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:

- Number of operations with Profit > 115%
- Percentage of operations with Profit > 115%
- Number of operations with Profit between 100% and 105%
- 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

938 Views

7 Replies

ChennaiahNallani

Creator III

2018-03-08
06:14 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

try this

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

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

830 Views

brunobertels

Master

2018-03-08
06:26 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

830 Views

agigliotti

Partner - Master III

2018-03-08
07:17 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

830 Views

anibal_marsis

Contributor III

2018-03-08
08:34 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

830 Views

anibal_marsis

Contributor III

2018-03-08
08:35 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

830 Views

anibal_marsis

Contributor III

2018-03-08
08:55 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

830 Views

agigliotti

Partner - Master III

2018-03-08
09:22 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

830 Views