Hello colleagues!
Could you please try to help me.
I've this same data set
SALES:
LOAD * INLINE
[
FY, DATE, FYPERIOD, CUSTOMER, ITEM, PRICE, QTY
2021, 20210705, 202111, C1, 0001, 100, 20
2021, 20210706, 202111, C1, 0001, 100, 20
2021, 20210707, 202111, C2, 0001, 110, 30
2021, 20210708, 202111, C2, 0001, 110, 40
2021, 20210805, 202112, C1, 0001, 100, 20
2021, 20210806, 202112, C1, 0001, 100, 20
2021, 20210807, 202112, C3, 0001, 200, 30
2021, 20210808, 202112, C3, 0001, 200, 40
2021, 20210705, 202111, C1, 0002, 1000, 20
2021, 20210706, 202111, C1, 0002, 1000, 20
2021, 20210707, 202111, C2, 0002, 1100, 30
2021, 20210708, 202111, C2, 0002, 1100, 40
2021, 20210805, 202112, C1, 0002, 1000, 20
2021, 20210806, 202112, C1, 0002, 1000, 20
2021, 20210807, 202112, C3, 0002, 2000, 30
2021, 20210808, 202112, C3, 0002, 2000, 40
2022, 20220705, 202211, C1, 0001, 500, 20
2022, 20220706, 202211, C1, 0001, 500, 20
2022, 20220707, 202211, C2, 0001, 510, 30
2022, 20220708, 202211, C2, 0001, 510, 40
2022, 20220805, 202212, C1, 0001, 500, 20
2022, 20220806, 202212, C1, 0001, 500, 20
2022, 20220807, 202212, C3, 0001, 600, 30
2022, 20220808, 202212, C3, 0001, 600, 40
2022, 20220705, 202211, C1, 0002, 5000, 20
2022, 20220706, 202211, C1, 0002, 5000, 20
2022, 20220707, 202211, C2, 0002, 5100, 30
2022, 20220708, 202211, C2, 0002, 5100, 40
2022, 20220805, 202212, C1, 0002, 5000, 20
2022, 20220806, 202212, C1, 0002, 5000, 20
2022, 20220807, 202212, C3, 0002, 6000, 30
2022, 20220808, 202212, C3, 0002, 6000, 40
];
Fiscal Year, Fiscal year period, Date of Sale, Customer, Item, Price & Qty
I need to calculate per Item/FY Period - Sales value(Qty*Price) in current FY period and Sales Value in last weighted average price (per FY period) for previous year (sum(Price*Qty)/Sum(qty)).
| ITEM |
FYPERIOD |
LastPrice FY21 |
SalesValue FY22 |
Sales volume FY22 |
Sales value FY22 in FY21 prices |
| 0001 |
202211 |
106,36 |
55700 |
110 |
11699,6 |
| 0001 |
202212 |
106,36 |
62000 |
110 |
11699,6 |
| 0002 |
202211 |
1063,64 |
557000 |
110 |
117000,4 |
| 0002 |
202212 |
1063,64 |
620000 |
110 |
117000,4 |
I can get last price for Item
Selection:
FY=2022
Dimension: Item
Expression: num(subfield(CONCAT({<FY={"$(=MAX(FY)-1)"},FYPERIOD=>} aggr(sum({<FY=,FYPERIOD=>}PRICE*QTY)/sum({<FY=,FYPERIOD=>}QTY),ITEM,FY,FYPERIOD),';',FYPERIOD),';',1))
And it works correctly.
| ITEM |
LastPrice |
| 1 |
106,36 |
| 2 |
1063,64 |
But when i've add FY periods to chart dimension, last price not calculated as expected.
| ITEM |
FYPERIOD2 |
LastPrice |
Sales value |
| 1 |
202111 |
106,36 |
0 |
| 1 |
202112 |
163,64 |
0 |
| 1 |
202211 |
|
|
| 1 |
202212 |
|
|
| 2 |
202111 |
1063,64 |
0 |
| 2 |
202112 |
1636,36 |
0 |
| 2 |
202211 |
|
|
| 2 |
202212 |
|
|
I've attach sample application. Focus on FY22.
Thank you
