FirstSortedValue + aggr + set analisys

AntonB53 — Sat, 08 Jan 2022 13:52:33 GMT

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

topic FirstSortedValue + aggr + set analisys in QlikView

FirstSortedValue + aggr + set analisys