How to query 3 month running average from SSAS Multidimensional Cube (MDX) for each date? -

-

i have ssas multidimensional cube , need query 3 month running average each day in date range.

for example, 2016-04-25 must data from 2016-01-01 2016-03-31. can't use query (because don't know how days must lag till previous month):

with member [measures].[salesamount 3m average] ( sum(     ([date].[date].currentmember.lag(90) :     ([date].[date].currentmember.lag(1),     [measures].[salesamount]     ) )

i guess need use ancestor function month , use lag month granularity.

ok, let's try one:

with member [measures].[salesamount 3m average] ( sum(     (ancestor ( [date].[date].currentmember, [date].[month] )).lag(3) :     (ancestor ( [date].[date].currentmember, [date].[month] )).lag(1),     [measures].[salesamount]     ) ) select { [measures].[salesamount 3m average] } on columns, { [date].[date].&[2016-01-01t00:00:00] : [date].[date].&[2016-02-28t00:00:00]} on rows [cube]

unfortunately, query doesn't work (returns null).

how solve problem?

updated: ok, tryed query member caption:

member [measures].[test] ( (ancestor      (   [date].[date].currentmember,          [date].[date].[month] )     ).item(0).member_caption )

and i'm getting not month caption, date(day) - same date [date].[date].currentmember.

then tryed queries:

--first try member [measures].[test] ( isancestor([date].[date].currentmember, [date].[month].&[2016-05-01t00:00:00]) )   --second try member [measures].[test] ( isancestor([date].[date].currentmember, [date].[date].[month].&[2016-05-01t00:00:00]) )   --third try member [measures].[test] ( isancestor([date].[date].currentmember, [date].[year -  quarter -  month -  date].[month].&[2016-05-01t00:00:00]) )

in queries result "false". so, month member not ancestor date members??? i'm confused.

my date dimension looks this:

enter image description here

update 2:

member [measures].[test] ( (ancestor      (   [date].[date].currentmember,          1 )     ).member_caption )

returns: all

your code seems fine. i'd add item(0) in couple of places make things more obvious shouldn't change much

with    member [measures].[salesamount 3m average]      sum     (         ancestor         (           [date].[calendar].currentmember          ,[date].[calendar].[month]         ).item(0).lag(3)       :          ancestor         (           [date].[calendar].currentmember          ,[date].[calendar].[month]         ).item(0).lag(1)      ,[measures].[internet sales amount]     )  select    {[measures].[salesamount 3m average]} on columns  ,{       [date].[calendar].[date].&[20060222]     :        [date].[calendar].[date].&[20060722]   } on rows [adventure works];

returns this:

enter image description here

check level expressions correct:

[date].[date] ?

and 

[date].[month] ?

also further test return?

with    member [measures].[test1]      ancestor     (       [date].[date].currentmember      ,[date].[month]     ).item(0).lag(3).member_caption    member [measures].[test2]      ancestor     (       [date].[date].currentmember      ,[date].[month]     ).item(0).lag(1).member_caption  select    {     [measures].[test1]    ,[measures].[test2]   } on columns  ,{       [date].[date].&[2016-01-01t00:00:00]     :        [date].[date].&[2016-02-28t00:00:00]   } on rows [cube];

ancestor function between different levels of same hierarchy ie:

enter image description here

what means [date].[date] , [date].[month] not related via ancestor. try this:

with  member [measures].[salesamount 3m average]   (     sum(       ancestor (           [date].[year -  quarter -  month -  date].currentmember,          [date].[year -  quarter -  month -  date].[month] ).lag(3)         :       ancestor(           [date].[year -  quarter -  month -  date].currentmember         ,[date].[year -  quarter -  month -  date].[month] ).lag(1)       ,[measures].[salesamount]     )   ) select  { [measures].[salesamount 3m average] } on columns, {    [date].[year -  quarter -  month -  date].[date].&[2016-01-01t00:00:00]  : [date].[year -  quarter -  month -  date].[date].&[2016-02-28t00:00:00] //i suspect above 2 lines can replaced following: //  [date].[date].&[2016-01-01t00:00:00] //: [date].[date].&[2016-02-28t00:00:00] }     on rows [cube];

note

this should return all because hierarchy [date].[date] has 2 levels ... leaf level dates single level above all:

member [measures].[test] ( (ancestor      (   [date].[date].currentmember,          1 )     ).member_caption )

Comments

Post a Comment

Popular posts from this blog

javascript - Feed FileReader from server side files -

-
i´m starting customize/improve old audio editor project. can import audio tracks canvas via drag&drop computer. thing use audio tracks stored in server clicking on list of available tracks... instead of use <input type="file"> tags. how can read server side files filereader?ajax perhaps? in advance. this code file reader: player.prototype.loadfile = function(file, el) { //console.log(file); var reader = new filereader, filetypes = ['audio/mpeg', 'audio/mp3', 'audio/wave', 'audio/wav'], = this; if (filetypes.indexof(file.type) < 0) { throw('unsupported file format!'); } reader.onloadend = function(e) { if (e.target.readystate == filereader.done) { // done == 2 $('.progress').children().width('100%'); var onsuccess = function(audiobuffer) { $(el).trigger('audiee:fileloaded', [audiobuffer, file]); ...
Read more

How to Change swipe Tab Title color in java Android -

-
by default, view pager(swipe tabs) tab title color white want change orange. can 1 me please or share if have tutorial. thanks... use style in styles.xml , assign style tab layout. <style name="mymaterialtheme.tablayout" parent="widget.design.tablayout"> <item name="tabindicatorcolor">@android:color/white</item> <item name="tabindicatorheight">3dp</item> <item name="tabtextappearance">@style/mymaterialtheme.tablayout.textappearance</item> <item name="tabselectedtextcolor">@android:color/white</item> <item name="android:background">@color/colorprimary</item> </style> <style name="mymaterialtheme.tablayout.textappearance" parent="textappearance.design.tab"> <item name="android:textsize">14sp</item> <item name="android:textcolor">@color/tab_text_inactive</item> ...
Read more

Using globs in Perl replace one liner in TCL script -

-
i want run 1 perl 1 liner in tcl script below: exec perl -i -pe {s/substring/replacing_string/g} testfile; this works fine. but, if want modify files below: exec perl -i -pe {s/substring/replacing_string/g} *; it gives me error message: can't open '*': no such file or directory. while executing exec perl -i -pe {s/substring/replacing_string/g} *; i tried bracing '*', did not solve problem. requesting help... assuming files a , b , , c present in current working directory, executing echo * in shell prints a b c . because shell command evaluator recognizes wildcard characters , splices in list of 0 or more file names wildcard expression found. tcl's command evaluator not recognize wildcard characters, passes them unsubstituted command invoked. if command can work wildcards so. exec command doesn't, means pass wildcard expression shell command named command string. testing this, get % exec echo * * because asked shell exe...
Read more