Colleagues, good afternoon! There is no lastchild type for measures by default in Kylin. Therefore, a problem arises when calculating such indicators as the stock. Monthly stock = stock on the last day of the month, stock for the year = stock on the last day of the year, stock for several days = stock on the last of these days etc.
To solve this problem, the following MDX script was developed:
IIF
(
[01 DATE].[Y-Q-M-D-Hierarchy].Level
IS
[01 DATE].[Y-Q-M-D-Hierarchy].[MONTH]
,Sum
(
[01 DATE].[Y-Q-M-D-Hierarchy].CurrentMember.LastChild
,[Measures].[STOCK QTY]
)
,IIF
(
[01 DATE].[Y-Q-M-D-Hierarchy].Level
IS
[01 DATE].[Y-Q-M-D-Hierarchy].[QUARTER]
,Sum
(
[01 DATE].[Y-Q-M-D-Hierarchy].CurrentMember.LastChild.LastChild
,[Measures].[STOCK QTY]
)
,IIF
(
[01 DATE].[Y-Q-M-D-Hierarchy].Level
IS
[01 DATE].[Y-Q-M-D-Hierarchy].[YEAR]
,Sum
(
[01 DATE].[Y-Q-M-D-Hierarchy].CurrentMember.LastChild.LastChild.LastChild
,[Measures].[STOCK QTY]
)
,[Measures].[STOCK QTY]
)
)
)
This MDX script solves the problem:
- Monthly stock = stock on the last day of the month.
- Stock for the year = stock on the last day of the year.
But with a multi-select of hierarchy elements, the sum of the Stock is calculated, and not the last child (attached a screenshot).
How can I fix the calculation of stock in multiple selection?