Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Do you have sample to test?
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
The same please put in QVW and share with us, We will look into that..
Please find attached
Use this in straight table, If you want same number?
Avg(Aggr(sum(Line_Value)/Count(DISTINCT Date), Date))
Are you looking to see 8.35 in the straight table like this?
Try this expression
Avg(TOTAL aggr(sum(Line_Value),Date))
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))