avg(aggr in a straight table

[shahafei2]

There is a way to show the same value in a straight table(with one dimension) 

and in chart bar(2 dimensions) ?

In the chart bar i presented the sum(line_value) by date and i have some reference lines that present the Avg. the Avg.+Stdev ,the 98%, and the Max

for example i use :

num(Avg(aggr(sum(Line_Value),Date)),'#,##0') 

for the Avg. reference line

from the other hand i have a straight table that supposed to present the group and the category information (as dimensions)

and I would like to present the reference lines from the bar chart as expressions in the  straight table

the problem is that i should use set analysis in order to filter the Line_Type for Line_Type='A' or Line_Type='B' only

How should i do it in order to receive the same totals as i get in the bar chart?

[Anil_Babu_Samineni]

Do you have sample to test?

[shahafei2]

Item_Master_Data:

Load * Inline [ 

SKU, Category, Group ,  A, B, C,

1111,  A , A , 20, 160 , 480 

1112,  A , B , 50, 250 , 1250

1113, A , C , 1, 40, 120

1121, B , A ,150 , 450 , 1800

1122, B , B,  20, 160 , 480 

1123, B, D ,  20, 160 , 480 

1135, C, E, 50, 250 , 1250

];

Orders:

Load * Inline [ 

OrderID, Date, SKU, Qty, Key

9999991, 01/01/2018 , 1122 , 1800 ,9999991-01/01/2018-1122 

9999992, 01/01/2018, 1123, 500, 9999992- 01/01/2018-1123

9999992, 01/01/2018, 1121, 480, 9999992-01/01/2018-1121

9999992, 01/01/2018, 1111, 480, 9999992-01/01/2018-1111

9999994, 02/01/2018, 1122, 1550,9999994-02/01/2018-1122

];

Line_Value:

Load * Inline [ 

Key,Line_Type, Line_Value

// The Line Value is calculated based on the ceil(Qty/C,0) when for the B and A we are using the rest of the Qty for (A does not ceil) example:

For key:

9999991-01/01/2018-1122

The Order Qty was 1800 

When 

A= 20

B= 160

C= 480

So the calculation is 

Line_Type C = Ceil(1800/480,0) = Ceil(3.75,0) = 3    When the Rest is 360 so

Line_Type B = Ceil(360/160,0) = Ceil(2.25,0) = 2 When the Rest is 40 so 

Line_Type A = (40/20) = 2

//

9999991-01/01/2018-1122 , C, 3

9999991-01/01/2018-1122 , B, 2

9999991-01/01/2018-1122 , A, 2

9999992- 01/01/2018-1123, C, 1

9999992- 01/01/2018-1123, B, 0

9999992- 01/01/2018-1123, A, 1

9999992-01/01/2018-1121, C, 0 

9999992-01/01/2018-1121, B, 1

9999992-01/01/2018-1121, A, 0.2

9999992-01/01/2018-1111, C,1

9999992-01/01/2018-1111, B,0

9999992-01/01/2018-1111, A,0

9999994-02/01/2018-1122,C,3

9999994-02/01/2018-1122,B,0

9999994-02/01/2018-1122,A,5.5

];

In that case the bar chart will show two dates for A and C line values

01/01/2018,A,3.2

01/01/2018,C,5

02/01/2018,A,5.5

02/01/2018,C,3

And the Avg. reference line will present

8.35

But the straight table will present

Category, Group , Expression - Avg Line_Type_A+C: sum({<Line_Type ={’A’,’C’}>},Line_Value)/Count(distinct Date)

A , A , (1)/1=1

B , A ,(0.2)/1 =0.2

B , B,  ((3+2)+(3+5.5))/2 =  6.75

B, D , (1+1) = 2

And the total for the expression will be  = 9.95

[Anil_Babu_Samineni]

The same please put in QVW and share with us, We will look into that..

[shahafei2]

Please find attached

[Anil_Babu_Samineni]

Use this in straight table, If you want same number?

Avg(Aggr(sum(Line_Value)/Count(DISTINCT Date), Date))

[sunny_talwar]

Are you looking to see 8.35 in the straight table like this?

Try this expression

Avg(TOTAL aggr(sum(Line_Value),Date))

[shahafei2]

Thank you all,

I think that i found the solution that i look for by using

sum({<Line_Type={'A','B,'C'}>}Line_Value)/Count(DISTINCT total(Date))