I have a project in tableau and i want count days in month marked in the filter "Fiscal time hierarchy". For example i mark on filter 2019.07 and my results is 31 and when also mark 2019.06 (+2019.07) i want results 61.
My tableau is connect to server so i can't change dimension to measures. I have to have a MDX query - in tableau "Calculated Members"
For today i prepared some code, but i have error when i mark on filter more that one time. Is working for one month or one year, but not working for two month, two year.
My mdx:
count( descendants([Time].[Fiscal Time Hierarchy].CurrentMember, 3))
Results for filtering 2019.07 -> 31 is correct
Results for filtering 2019.07 + 2019.06 ->
ERROR
Error text for two times: An error occurred while communicating with data source 'Model (Ariadne)'.
Error Code: A7AED4D2
Database error 0x80004005:
?The MDX CURRENTMEMBER function failed to execute because the coordinate for the 'Fiscal Year' attribute contains a set.
Technical details:
RootActivityId: 251a2e39-6466-46d3-bec0-c5e305c186eb
Data (UTC): 8/1/2019 7:10:12 AM
; The query: WITH
MEMBER [Measures].[daycount] AS
'count( descendants([Time].[Fiscal Time Hierarchy], 3))',
SOLVE_ORDER = 0, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_556757519052849152] AS
'([Measures].[Trans Revenue Nett (LC)] - [Measures].[Revenue BDG 2020 before ADJ (LC)])',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_995295533060087809] AS
'IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))),NULL,DATEADD("d",-1,IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))))),NULL,DAY(IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))),NULL,DATEADD("d",-1,IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))))))',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_591378942957641731] AS
'[Measures].[Trans Revenue Nett (LC)]',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_591378942953566209] AS
'(1 - ([Measures].[Revenue BDG 2020 before ADJ (LC)] / (([Measures].[Trans Revenue Nett (LC)] * IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))),NULL,DATEADD("d",-1,IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))))),NULL,DAY(IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))),NULL,DATEADD("d",-1,IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))))))) / IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW())))),NULL,DAY(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW()))))))))',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_322851815536898052] AS
'(1 - ([Measures].[Revenue FC1 2020 before ADJ (LC)] / (([Measures].[Trans Revenue Nett (LC)] * IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))),NULL,DATEADD("d",-1,IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))))),NULL,DAY(IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))),NULL,DATEADD("d",-1,IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))))))) / IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW())))),NULL,DAY(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW()))))))))',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_322851815528308738] AS
'(1 - ([Measures].[Revenue FC1 2020 before ADJ (LC)] / [Measures].[Trans Revenue Nett (LC)]))',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_322851815536275459] AS
'((([Measures].[Trans Revenue Nett (LC)] * IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))),NULL,DATEADD("d",-1,IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))))),NULL,DAY(IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))),NULL,DATEADD("d",-1,IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))))))) / IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW())))),NULL,DAY(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW())))))) - [Measures].[Revenue FC1 2020 before ADJ (LC)])',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_995295533059760128] AS
'IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW())))),NULL,DAY(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW())))))',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_591378943005073413] AS
'((([Measures].[Trans Revenue Nett (LC)] * IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))),NULL,DATEADD("d",-1,IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))))),NULL,DAY(IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))),NULL,DATEADD("d",-1,IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))))))) / IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW())))),NULL,DAY(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW())))))) - [Measures].[Trans Revenue Nett (LC)])',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_995295533060726786] AS
'(([Measures].[Trans Revenue Nett (LC)] * IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))),NULL,DATEADD("d",-1,IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))))),NULL,DAY(IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))),NULL,DATEADD("d",-1,IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))))))) / IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW())))),NULL,DAY(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW()))))))',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_995295533073104899] AS
'DateValue(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW()))))',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_322851815528140801] AS
'([Measures].[Trans Revenue Nett (LC)] - [Measures].[Revenue FC1 2020 before ADJ (LC)])',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_591378942953381888] AS
'((([Measures].[Trans Revenue Nett (LC)] * IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))),NULL,DATEADD("d",-1,IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))))),NULL,DAY(IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))),NULL,DATEADD("d",-1,IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))))))) / IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW())))),NULL,DAY(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW())))))) - [Measures].[Revenue BDG 2020 before ADJ (LC)])',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_591378942958161924] AS
'[Measures].[Revenue BDG 2020 before ADJ (LC)]',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_556757519053275138] AS
'(1 - ([Measures].[Revenue BDG 2020 before ADJ (LC)] / [Measures].[Trans Revenue Nett (LC)]))',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_322851815527907328] AS
'[Measures].[Revenue FC1 2020 before ADJ (LC)]',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
SELECT
{[Measures].[daycount]} DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME],[MEMBER_CAPTION] ON COLUMNS
FROM [Model]
WHERE
StripCalculatedMembers(CROSSJOIN(
Intersect(
{[Platform - Attributes].[Declared Seller Type].&[B2C]:[Platform - Attributes].[Declared Seller Type].&[C2C]},
AddCalculatedMembers( Except(
[Platform - Attributes].[Declared Seller Type].Members,
{[Platform - Attributes].[Declared Seller Type].Levels(0).Members}) )),
CROSSJOIN(
{[Site].[Country Hierarchy].[Country].&[Poland]},
{[Time].[Fiscal Time Hierarchy].[Fiscal Month].&[2019-06]:[Time].[Fiscal Time Hierarchy].[Fiscal Month].&[2019-07]})))
WITH
MEMBER [Measures].[daycount] AS
'count( descendants([Time].[Fiscal Time Hierarchy], 3))',
SOLVE_ORDER = 0, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_556757519052849152] AS
'([Measures].[Trans Revenue Nett (LC)] - [Measures].[Revenue BDG 2020 before ADJ (LC)])',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_995295533060087809] AS
'IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))),NULL,DATEADD("d",-1,IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))))),NULL,DAY(IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))),NULL,DATEADD("d",-1,IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))))))',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_591378942957641731] AS
'[Measures].[Trans Revenue Nett (LC)]',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_591378942953566209] AS
'(1 - ([Measures].[Revenue BDG 2020 before ADJ (LC)] / (([Measures].[Trans Revenue Nett (LC)] * IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))),NULL,DATEADD("d",-1,IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))))),NULL,DAY(IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))),NULL,DATEADD("d",-1,IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))))))) / IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW())))),NULL,DAY(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW()))))))))',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_322851815536898052] AS
'(1 - ([Measures].[Revenue FC1 2020 before ADJ (LC)] / (([Measures].[Trans Revenue Nett (LC)] * IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))),NULL,DATEADD("d",-1,IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))))),NULL,DAY(IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))),NULL,DATEADD("d",-1,IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))))))) / IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW())))),NULL,DAY(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW()))))))))',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_322851815528308738] AS
'(1 - ([Measures].[Revenue FC1 2020 before ADJ (LC)] / [Measures].[Trans Revenue Nett (LC)]))',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_322851815536275459] AS
'((([Measures].[Trans Revenue Nett (LC)] * IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))),NULL,DATEADD("d",-1,IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))))),NULL,DAY(IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))),NULL,DATEADD("d",-1,IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))))))) / IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW())))),NULL,DAY(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW())))))) - [Measures].[Revenue FC1 2020 before ADJ (LC)])',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_995295533059760128] AS
'IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW())))),NULL,DAY(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW())))))',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_591378943005073413] AS
'((([Measures].[Trans Revenue Nett (LC)] * IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))),NULL,DATEADD("d",-1,IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))))),NULL,DAY(IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))),NULL,DATEADD("d",-1,IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))))))) / IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW())))),NULL,DAY(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW())))))) - [Measures].[Trans Revenue Nett (LC)])',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_995295533060726786] AS
'(([Measures].[Trans Revenue Nett (LC)] * IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))),NULL,DATEADD("d",-1,IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))))),NULL,DAY(IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))),NULL,DATEADD("d",-1,IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))))))) / IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW())))),NULL,DAY(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW()))))))',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_995295533073104899] AS
'DateValue(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW()))))',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_322851815528140801] AS
'([Measures].[Trans Revenue Nett (LC)] - [Measures].[Revenue FC1 2020 before ADJ (LC)])',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_591378942953381888] AS
'((([Measures].[Trans Revenue Nett (LC)] * IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))),NULL,DATEADD("d",-1,IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))))),NULL,DAY(IIF(IsEmpty(IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))),NULL,DATEADD("d",-1,IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))),NULL,DATEADD("m",DATEDIFF("m", CDate(34700), IIF(IsEmpty(INT(NOW())),NULL,DATEADD("m",1,INT(NOW())))), CDate(34700)))))))) / IIF(IsEmpty(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW())))),NULL,DAY(IIF(IsEmpty(INT(NOW())),NULL,DATEADD("d",-1,INT(NOW())))))) - [Measures].[Revenue BDG 2020 before ADJ (LC)])',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_591378942958161924] AS
'[Measures].[Revenue BDG 2020 before ADJ (LC)]',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_556757519053275138] AS
'(1 - ([Measures].[Revenue BDG 2020 before ADJ (LC)] / [Measures].[Trans Revenue Nett (LC)]))',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
MEMBER [Measures].[Calculation_322851815527907328] AS
'[Measures].[Revenue FC1 2020 before ADJ (LC)]',
SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
SELECT
{[Measures].[daycount]} DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME],[MEMBER_CAPTION] ON COLUMNS
FROM [Model]
WHERE
StripCalculatedMembers(CROSSJOIN(
Intersect(
{[Platform - Attributes].[Declared Seller Type].&[B2C]:[Platform - Attributes].[Declared Seller Type].&[C2C]},
AddCalculatedMembers( Except(
[Platform - Attributes].[Declared Seller Type].Members,
{[Platform - Attributes].[Declared Seller Type].Levels(0).Members}) )),
CROSSJOIN(
{[Site].[Country Hierarchy].[Country].&[Poland]},
{[Time].[Fiscal Time Hierarchy].[Fiscal Month].&[2019-06]:[Time].[Fiscal Time Hierarchy].[Fiscal Month].&[2019-07]})))
User contributions licensed under CC BY-SA 3.0