locked
Applying Non-Empty on All-To-Date calculation (MDX/SSAS) RRS feed

  • Question

  • Hi all, 

    I have a All-To-Date calulcation which sums up actuals for each day (from the beginning to the current date)
    MDX script

    SCOPE([Type].[Type].[ATD]);  
      THIS = AGGREGATE({NULL: [Time].[Date].CURRENTMEMBER},[Type].[Type].[Actuals]);
    END SCOPE;

    Basically it calculates as expected, but a quick analysis in MS Excel shows all dates instead of only those dates which have an actual value.

    Any idea how I can accomplish this appropriately? I was thinking about a NON-Empty, but where do I place this?

    SCOPE([Type].[Type].[ATD]);  
      THIS = AGGREGATE({NULL: [Time].[Date].CURRENTMEMBER}, NON EMPTY([Type].[Type].[Actuals])); ==> Results in an error
    END SCOPE;

    In Pictures :-)

     

    should become this 



    Cheers

    Monday, March 7, 2016 7:12 AM

Answers

  • Hi,try this:

    SCOPE([Type].[Type].[ATD]); 
      THIS
    = IIF(([Time].[Date].CURRENTMEMBER,[Type].[Type].[Actuals]) = NULL,NULL,AGGREGATE({NULL: [Time].[Date].CURRENTMEMBER}, [Type].[Type].[Actuals]));
    END SCOPE;

    • Marked as answer by MichaelMDX Monday, March 7, 2016 9:42 AM
    Monday, March 7, 2016 9:20 AM

All replies

  • Hi,try this:

    SCOPE([Type].[Type].[ATD]); 
      THIS
    = IIF(([Time].[Date].CURRENTMEMBER,[Type].[Type].[Actuals]) = NULL,NULL,AGGREGATE({NULL: [Time].[Date].CURRENTMEMBER}, [Type].[Type].[Actuals]));
    END SCOPE;

    • Marked as answer by MichaelMDX Monday, March 7, 2016 9:42 AM
    Monday, March 7, 2016 9:20 AM
  • Love you! Works like a charm!
    Monday, March 7, 2016 9:42 AM