topic Re: Expression breakdown in QlikView

Expression breakdown

didierodayo — Wed, 30 Nov 2016 10:02:03 GMT

Hello,

I need some help to figure out what this calculated dimension is doing. I have added a snapshot of the output.

Thanks for your assistance.

Calculated dimension

=Dual(Replace(Class(
        Aggr(Sum({$<Metric={'vrf'}>} [Metric Value Peak])/
                                Only({$<Metric={'vrf'}>} [Tunnel Limit])*100, [Pole Name])
, 10), '<= x <','-'),

Num(SubField(Class(
        Aggr(Sum({$<Metric={'vrf'}>} [Metric Value Peak])/
                                Only({$<Metric={'vrf'}>} [Tunnel Limit])*100, [Pole Name])
, 10),'<=',1),'0')
)

expression

Count(DISTINCT [Pole Name])

Re: Expression breakdown

rubenmarin — Wed, 30 Nov 2016 10:14:51 GMT

Hi Didier, from inside () to outside ():

- Aggr(): Calculates the expression for each [Pole Name]

- Class(): Groups the results of the expression in a range of 10 (2nd parameter of the function)

- Replace(): Changes the default '<= x <' of the class() function to '-'

- Dual(): Used when you want store a string different than the number (or viceversa, like in months: january is Dual('Jan', 1)). In that case the 2nd part of dual is used to sort the dimension.

- Subfield(): Splits a string, in this case it keeps the value before '<=', so the first number of the Class() funtion, the dimension values wil be sorted by default by this number.

Re: Expression breakdown

stabben23 — Wed, 30 Nov 2016 10:22:58 GMT

I'll give it a try:

Dual(Replace(Class(
        Aggr(Sum({$<Metric={'vrf'}>} [Metric Value Peak])/
                                Only({$<Metric={'vrf'}>} [Tunnel Limit])*100, [Pole Name])
, 10), '<= x <','-'), //this part will build interval from 0<=xx in 10 classes, like 0,10,20,30 aso. it will also replace the <= x (that will comes in between when you use class function) with a '-'. The sum will get values around 0.1 and *100 so it will fit into class with base of 10.

Num(SubField(Class(
        Aggr(Sum({$<Metric={'vrf'}>} [Metric Value Peak])/
                                Only({$<Metric={'vrf'}>} [Tunnel Limit])*100, [Pole Name])
, 10),'<=',1),'0')
) //this will find the value to left of <= sign ex 0 when class is 0<=10 and it will do a numeric value as an integer. and you will get a range 0,10,20,30,40,50... as dimension.

Maybe someone else could explain it better.

Re: Expression breakdown

MK_QSL — Wed, 30 Nov 2016 10:32:26 GMT

You can read more information class funtion from below link

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/ConditionalFunctions/class.htm

1) Sum({$<Metric={'vrf'}>} [Metric Value Peak])/Only({$<Metric={'vrf'}>} [Tunnel Limit])*100

This is your base expression.

Objective is to create range of above function result with interval of 10.

This can be done as below

Class(Sum({$<Metric={'vrf'}>} [Metric Value Peak])/Only({$<Metric={'vrf'}>} [Tunnel Limit])*100,10)

2) Above Class will give you results something like below

0 <= x < 10

10 <= x < 20

20 <= x < 30

30 <= x < 40

and so on..depending upon the values you are getting

3) Now comes the tricky portion.

Mostly user don't want to see '<= x <'

So here comes Replace function..

It will replace <= x < with '-'

4) Issue of using Replace is ..

Class Function gives you numeric ranges.. I mean the range you are getting can be sorted using sort by Numeric Value but Replace is a string function and it will convert your whole expression value in String/Text, which will now not allow you to sort by Numeric

5) To avoid this we can use Dual funtion.

Dual(Text, Number) will show value as Text and can sort as number.

to make that the 2nd portion of class is used.

6) I am pretty much sure that this can be simplified as below.

=Dual(

Replace(Aggr(Class(Sum({$<Metric={'vrf'}>} [Metric Value Peak])/Only({$<Metric={'vrf'}>} [Tunnel Limit])*100,10),[Pole Name]),'<= x <' ,'-'),

Aggr(Class(Sum({$<Metric={'vrf'}>} [Metric Value Peak])/Only({$<Metric={'vrf'}>} [Tunnel Limit])*100,10),[Pole Name])

)

Hope this helps

Re: Expression breakdown

didierodayo — Wed, 30 Nov 2016 10:38:43 GMT

Thanks So much all. You replies were all really helpful.