Sum Aggr cross multiple

[Amit_B]

Hi,

A client needs to calculate a certain forecast of sales. The forecasting is done with the help of a table of factors that is divided by month and product type. For example:

	Jan-Feb	Mar-Apr	...
Type A	1.2	0.75
Type B	1.8	0.92
...

According to the formula, it is required to multiply the sales of each type of product by the factor of the relevant month. For example:
Actual sales of Type A in Jan&Feb is 100 and in Mar&Apr is 200.. Actual sales of Type B in Jan&Feb is 500 and in Mar&Apr is 150..
So, Predict = (100*1.2)+(200*0.75)+...+ (500*0.75)+(150*0.92)+...

I tried to do with aggregations and with conditions on the months and doesn't work.
How Can I do it easily?

Thanks!

[Anil_Babu_Samineni]

@Amit_B Perhaps this, With this you will be getting forecast based on each product.

linest_b(total aggr(if(sum(Value),sum(Value)),product),product))

[Ahidhar · Accepted solution]

try something like this

tab1:
CrossTable(Period,Factor)
load * Inline
[
Category,Jan-Feb,Mar-Apr
Type A,1.2,0.75
Type B,1.8,0.92
];

left join(tab1)
tab:
load * Inline
[
Category,Period,Sales
Type A,Jan-Feb,100
Type A,Mar-Apr,200
Type B,Jan-Feb,500
Type B,Mar-Apr,150
];

then use pivot table Sales *Factor