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 - How to get current YouTube IDs via iMacros? -

-
i want viewed youtube video id , save csv via imacros, gets nothing. here js code: var videoid; videoid ="code:"; videoid +="url goto=https://www.youtube.com/watch?v=dbnywxdz_pa"+"\n"; videoid +="add !extract {{!urlcurrent}}"+"\n"; videoid +="set !var1 eval(\"var s=\"{{!urlcurrent}}\"; s.match((?<=watch\\?v=|/videos/|embed\\/)[^#\\&\\?]*);s[0]; \")"+"\n"; videoid +="add !extract {{!var1}}"+"\n"; videoid +="saveas type=extract folder=* file=url.csv"+"\n"; try this: var videoid = "url goto=https://www.youtube.com/watch?v=dbnywxdz_pa" + "\n"; videoid += "set !extract {{!urlcurrent}}" + "\n"; videoid += 'set !extract eval("\'{{!extract}}\'.match(/v=(.{11})/)[1];")' + "\n"; videoid += "saveas type=extract folder=* file=url.csv" + "\n"; iimplay
Read more

c# - Maintaining a program folder in program files out of date? -

-
for program i'm writing in vs c# wpf need store user related information on user's computer. far knew has been done creating folder in c:\program files , adding whatever program related info folder in subfolders or whatever. after doing browsing came across lot of people saying method out of date because access may denied create folder there, works administrative accounts, etc.. 1 site suggested saving c:\users\username\appdata\roaming or c:\users\username\appdata\local. question best , date method saving program data users computer? also take @ msdn article . it uwp apps have general idea put if developing wpf app. you may need these folders: environment.specialfolder.applicationdata environment.specialfolder.localapplicationdata environment.specialfolder.commonapplicationdata
Read more

emulation - Android map show my location didn't work -

-
i have been struggle current location in google map, tried official map demo, not working also. shows getmylocation button on upper right, didn't show blue dot on map current location. here code , i'm using emulator target api 23, guess may emulator's problem since code download directly github. advice appreciated. [screenshot][1] package com.example.mapdemo; import com.google.android.gms.maps.googlemap; import com.google.android.gms.maps.googlemap.onmylocationbuttonclicklistener; import com.google.android.gms.maps.onmapreadycallback; import com.google.android.gms.maps.supportmapfragment; import android.manifest; import android.content.pm.packagemanager; import android.os.bundle; import android.support.annotation.nonnull; import android.support.v4.app.activitycompat; import android.support.v4.content.contextcompat; import android.support.v7.app.appcompatactivity; import android.widget.toast; /** * demo
Read more