0

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:

  1. Monthly stock = stock on the last day of the month.
  2. 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?

whytheq
  • 34,466
  • 65
  • 172
  • 267
Ruslan
  • 1

0 Answers0