If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.
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% |
... | ... | ... | ... |
. What I would like to do is to add 4 desired metrics, which are:
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
try this
Count({<Operations= {"=Profit > 115"}>} Distinct Operations)
Avg({<Operations= {"=Profit > 115"}>} Operations)
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])
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])
Hi Chennaiah,
I am not available to achieve the "Percentage of operations with Profit > 115%". After checking my attributes I have realized that:
I am editing my question as Operations is not a master item but a calculated metric.
Thanks in advance for your help.
Anibal
Hi Chennaiah,
I am not available to achieve the "Percentage of operations with Profit > 115%". After checking my attributes I have realized that:
I am editing my question as Operations is not a master item but a calculated metric. Thanks in advance for your help.
Anibal
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
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])