Ideas to bring a stock value into a cube with flowing values

• m.rikus

  SSC Journeyman

  Points: 98

  More actions

  February 7, 2017 at 6:23 am

  #149350

  Hi there,
  first of a I have to appologize for my limited capabilities in English. It would make me glad to get some hints for a designing problem:

  We do an important share of sales by blanket agreements with our customers. A blanket agreement contains an item, quantity and startdate.
  Now we wish to see the open quantity of blanket aggreements at any time in our Sales Cube.

  For this purpose we added a column to fact table Sales wich contains the current open quantity for every sales doc, that is related to a blanket agreement.
  
  Further on we adapted the example for "Last ever non empty" from Chris Webb's Blog.
  It works  well, but only for leaf elements in time-dimension and any other aggregations lead to wrong results.

  CREATE MEMBER CURRENTCUBE.MEASURES.DAYSTODATE AS
  COUNT(NULL:[Datum Lieferung].[Datum].CURRENTMEMBER)-1
  ,VISIBLE=TRUE, DISPLAY_FOLDER = '99 RV Rest', ASSOCIATED_MEASURE_GROUP = 'VERKAUF';

  CREATE MEMBER CURRENTCUBE.MEASURES.HADSALE AS
  IIF(
    [Measures].[Absatz Rest RV]=0
    ,NULL
    ,MEASURES.DAYSTODATE
    )
  ,VISIBLE=TRUE, DISPLAY_FOLDER = '99 RV Rest', ASSOCIATED_MEASURE_GROUP = 'VERKAUF';

  SCOPE(MEASURES.[MAXDATE RV], [Datum Lieferung].[Kalender].[Datum].MEMBERS);
    THIS = MAX(NULL:[Datum Lieferung].[Kalender].CURRENTMEMBER, MEASURES.HADSALE);
  END SCOPE;

  CREATE MEMBER CURRENTCUBE.MEASURES.LASTSALE AS
  IIF(
    ISEMPTY(MEASURES.MAXDATE)
    ,NULL
    ,(
     [Measures].[Absatz Rest RV]
    ,[Datum Lieferung].[Kalender].[Datum].MEMBERS.ITEM([MEASURES].[MAXDATE RV])
    )
    )
  ,FORMAT_STRING = "#,##0,;-#,##0,", VISIBLE=TRUE, DISPLAY_FOLDER = '99 RV Rest', ASSOCIATED_MEASURE_GROUP = 'VERKAUF';

  I guess this approach is not appropriate.
  What is the best-practice to solve this kind of issue?

  Thanks in advance for any reply.

  Mathias