Qlik Community

New to Qlik Sense

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

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

set average of group as a variable

groupvalue
A14
A23
A53
B4
B63
B13
B7

When loading the data, I want to set variables of each group(A and B)

SET vAverageA = (average value of group A)//(14+23+53)/3

SET vAverageB = (average value of group B)//(4+63+13+7)/4

Does anybody know how to do this?

1 Solution

Accepted Solutions
sunny_talwar

May be this:

Table:

LOAD * Inline [

group, value

A, 14

A, 23

A, 53

B, 4

B, 63

B, 13

B, 7

];

AggrTable:

LOAD Avg(value) as Avg,

  group

Resident Table

Group By group

Order By group;

For i = 1 to FieldValueCount('group')

  vField = FieldValue('group', $(i));

  TempTable:

    NoConcatenate

    LOAD Avg

    Resident AggrTable

    Where group = '$(vField)';

   

    LET vAverage$(vField) = Peek('Avg');

    DROP Table TempTable;

NEXT i

Drop Table AggrTable;

View solution in original post

2 Replies
sunny_talwar

May be this:

Table:

LOAD * Inline [

group, value

A, 14

A, 23

A, 53

B, 4

B, 63

B, 13

B, 7

];

AggrTable:

LOAD Avg(value) as Avg,

  group

Resident Table

Group By group

Order By group;

For i = 1 to FieldValueCount('group')

  vField = FieldValue('group', $(i));

  TempTable:

    NoConcatenate

    LOAD Avg

    Resident AggrTable

    Where group = '$(vField)';

   

    LET vAverage$(vField) = Peek('Avg');

    DROP Table TempTable;

NEXT i

Drop Table AggrTable;

Not applicable
Author

Thank you so much!! It worked!!