topic Re: expression in QlikView

expression

adiarnon — Thu, 09 Mar 2017 07:22:44 GMT

hi,

i have an expression-

=COUNT({<Division={"=Aggr(Sum({<METRIC=,COMPONENETID={'49'}>}COMPONENTVALUE)/Sum({<METRIC=,COMPONENETID={'50'}>}COMPONENTVALUE)*100,Division)>70"}>}DISTINCT Division)

i have the value >70 in a field 'minvalue' how can i change the expression to take the >70 from the field?

adi

Re: expression

Anil_Babu_Samineni — Thu, 09 Mar 2017 07:37:13 GMT

May be this? I am not sure you are looking this

=COUNT({<Division={"=Aggr(Sum({<METRIC=,COMPONENETID={'49'}, value = {'$(=Min(value))'} >}COMPONENTVALUE) /

Sum({<METRIC=,COMPONENETID={'50'}, value = {'$(=Min(value))'} >}COMPONENTVALUE) *100,Division)>70"}>} DISTINCT Division)

Re: expression

PrashantSangle — Thu, 09 Mar 2017 07:42:19 GMT

Hi,

why not try simple count(if())

count(if(Sum(Aggr(Sum({<METRIC=,COMPONENETID={'49'}>}COMPONENTVALUE)/Sum({<METRIC=,COMPONENETID={'50'}>}COMPONENTVALUE)*100,Division))>70,distinct Division))

Regards,

Prashant

Re: expression

adamdavi3s — Thu, 09 Mar 2017 07:45:57 GMT

Hi Adi,

Is 70 the only value in the field? Is it linked to your other data?

Assuming its a singular value you could use something like this (sorry I might have the syntax slightly out)

=COUNT({<Division={"=Aggr(Sum({<METRIC=,COMPONENETID={'49'}>}COMPONENTVALUE)/Sum({<METRIC=,COMPONENETID={'50'}>}COMPONENTVALUE)*100,Division)={">$(=only({1}minvalue)"}"}>}DISTINCT Division)

Re: expression

adiarnon — Thu, 09 Mar 2017 08:42:16 GMT

its not only 70 and its not always >

it can be <20

=15

if it was just the number it was easy

Re: expression

adamdavi3s — Thu, 09 Mar 2017 09:07:57 GMT

OK but how is this number derived?

how is it linked to your data or how is it input?

Re: expression

adiarnon — Thu, 09 Mar 2017 09:12:05 GMT

i have a table with the fields metric_id, minvalue

in this screen only one metric_id must choosen

and then i have the only minvalue for this metric_id

Re: expression

adamdavi3s — Thu, 09 Mar 2017 09:18:20 GMT

Ok so in this example my formula should work with a tweak, however how do you tell if it is > or< or = ?

=COUNT({<Division={"=Aggr(Sum({<METRIC=,COMPONENETID={'49'}>}COMPONENTVALUE)/Sum({<METRIC=,COMPONENETID={'50'}>}COMPONENTVALUE)*100,Division)={">$(=minvalue)"}"}>}DISTINCT Division)

Re: expression

adiarnon — Thu, 09 Mar 2017 09:29:38 GMT

in the field minvalue i have a string LIKE-

'>70'

Re: expression

adamdavi3s — Thu, 09 Mar 2017 09:31:38 GMT

hmmmm OK.... let me set up some testing!

Re: expression

adamdavi3s — Thu, 09 Mar 2017 09:55:28 GMT

Ok give this a crack

=$(='COUNT({<Division={"=Aggr(Sum({<METRIC=,COMPONENETID={''49''}>}COMPONENTVALUE)/Sum({<METRIC=,COMPONENETID={''50''}>}COMPONENTVALUE)*100,Division)'&minvalue&'"}>}DISTINCT Division)')

Re: expression

adiarnon — Thu, 09 Mar 2017 10:07:28 GMT

tnx!!!

Re: expression

adamdavi3s — Thu, 09 Mar 2017 10:09:04 GMT

No worries, it was a fun challenge!

Please remember to mark any correct and helpful answers