topic Re: Get avg($Field) in QlikView

Get avg($Field)

Wed, 10 Dec 2014 07:34:10 GMT

Hello,

I am designing a table based on ColumnNames. The target is to display all columns which are like '*LeadTime' as rows.

So i am using the dimensions as

=if(wildmatch($Field,'*LeadTime'),SubField($Field,'_',2)) -Workshop

=if(wildmatch($Field,'*LeadTime'),SubField($Field,'_',1)) -Previous Falg

I want to calculate average

So when i tried =avg($Field), it is not giving results as required.

Please suggest, how I can acheive this.

Thanks,

Deepa

Re: Get avg($Field)

Anonymous — Wed, 10 Dec 2014 07:38:11 GMT

Can you attached your sample QVW

Re: Get avg($Field)

arulsettu — Wed, 10 Dec 2014 07:47:09 GMT

HI try this

=avg([$(=minstring($Field))])

Re: Get avg($Field)

Wed, 10 Dec 2014 07:49:41 GMT

Please find attahced the sample QVW file for reference

Re: Get avg($Field)

Wed, 10 Dec 2014 07:55:54 GMT

It always gives the average of min(filedname),

I am looking for average of filed name.

sorry..

Re: Get avg($Field)

anbu1984 — Wed, 10 Dec 2014 08:07:00 GMT

=avg(Len($Field))

Re: Get avg($Field)

Wed, 10 Dec 2014 08:59:47 GMT

Hi Anbu,

=avg(Len($Field))

it, considers average of the length of the field,

I am looking for average of field value

Re: Get avg($Field)

renjithpl — Wed, 10 Dec 2014 09:22:11 GMT

Hi Deepa,

Can you please show a sample output which you are expecting in an excel sheet.

Cheers

Ren

Re: Get avg($Field)

Anonymous — Wed, 10 Dec 2014 09:34:43 GMT

Hi Deepa

If you are looking for avg length of your fields then you can use =sum(len($Field))/count($Field)

Re: Get avg($Field)

Wed, 10 Dec 2014 09:39:40 GMT

I am not looking for the length of field names

I am looking to get the average of field values.

I want to evaluate the avg(Received_Beginning_Leadtime) for 1st row

avg(Beginning_Surfaced_Leadtime) for second row

Thanks

Re: Get avg($Field)

Anonymous — Wed, 10 Dec 2014 09:55:51 GMT

Hi Deepa ,

use this code in last column expression :

=if($Field ='Surfaced_HardCoating_Leadtime', avg(Surfaced_HardCoating_Leadtime),

if($Field ='Surfaced_TintingUV_Leadtime', avg(Surfaced_TintingUV_Leadtime),

if($Field ='Received_Beginning_Leadtime', avg(Received_Beginning_Leadtime),

if($Field ='Beginning_HardCoating_Leadtime', avg(Beginning_HardCoating_Leadtime),

if($Field ='Beginning_Surfaced_Leadtime', avg(Beginning_Surfaced_Leadtime),

if($Field ='Beginning_TintingUV_Leadtime', avg(Beginning_TintingUV_Leadtime),

if($Field ='Tinting_HardCoating_Leadtime', avg(Tinting_HardCoating_Leadtime)

)))))))

let me know if you are looking for something else

Re: Get avg($Field)

Anonymous — Wed, 10 Dec 2014 10:00:08 GMT

See below example

Re: Get avg($Field)

anbu1984 — Wed, 10 Dec 2014 10:02:09 GMT

$(=concat('if($Field=' & chr(39) & $Field & chr(39) & ',Avg({1}[' & $Field & '])', ',') & concat(right($Field&')',1)))

Chart with count of $Field values

Re: Get avg($Field)

Wed, 10 Dec 2014 10:04:45 GMT

Hi,

Thanks for the reply.

Yes, it works.

Is there any work around for this, instead of checking for the if($Field=

Re: Get avg($Field)

anbu1984 — Wed, 10 Dec 2014 10:06:03 GMT

Check my post above

Re: Get avg($Field)

marcus_sommer — Wed, 10 Dec 2014 11:29:33 GMT

What you are trying to do is to calculate results over fields which are in a crosstable-structure. Such calculations are possible but for the most calculations they are rather awkward and a "normal" data-structure would be better, maybe an additionally one.

http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/24/crosstable

- Marcus