excel - Datevalue calculation in MDX query -
i want create dynamic query updates each day.
so filter on todays report use
[report date].[report date].&[4226]
the 4226 coming from:
=datevalue("28-07-2017")-38718 or =today()-38718 (convert number)
38718 arbitrary number correct date cube.
edit:
here current query:
select non empty { [measures].[price fc] } on columns ( select ( -{ [agency].[nationality - consortium - agency].[nationality].&[111], [agency].[nationality - consortium - agency].[nationality].&[116], [agency].[nationality - consortium - agency].[nationality].&[242], [agency].[nationality - consortium - agency].[nationality].&[134] } ) on columns ( select ( { strtomember("[report date].[report date].&[" + str(datevalue(format(now(), "dd-mm-yyyy")) - 38718) + "]") } ) on columns ( select ( { [market].[market].[market].&[103] } ) on columns ( select ( { [travel type].[travel type].&[101], [travel type].[travel type].&[102], [travel type].[travel type].&[103] } ) on columns ( select ( { [departure date].[year].&[2017] } ) on columns [booking]))))) ( [departure date].[year].&[2017], [travel type].[travel type].currentmember, [market].[market].[market].&[103], strtomember("[report date].[report date].&[" + str(datevalue(format(now(), "dd-mm-yyyy")) - 38718) + "]") )
but says there no column detected in statement. have tried different date formats, ideas?
following tips thread:
i used cdbl instead of datevalue, gave me desired result!
strtomember("[report date].[report date].&[" + str(int(cdbl(now()) - 38718)) + "]")
Comments
Post a Comment