Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

How to load dimension with filter itself in MDX Query Generator

Hi all,

I used the MDX Query Generator for Qlikview to fetch the data from cube for my report. the problem is when I filter to just load the [Broadcast Year] with value = 2013-2014. the generated script doesn't include the [Broadcast Year] dimension. I try to insert [Broadcast Year] dimension in Load and CrossJoin section, but it doesn't work . Any suggestion for this case.

Works but doesn't include [Broadcast Year]:

LOAD

    "[Measures].[Number Of Units]" as [Number Of Units],

    "[Measures].[Gross Actual Cost]" as [Gross Actual Cost],

    "[Dim Agency].[Agency Name].[Agency Name].[MEMBER_CAPTION]" as [Agency Name],

    "[Dim Buy Type].[Buy Type Name].[Buy Type Name].[MEMBER_CAPTION]" as [Buy Type Name],

    "[Dim Client].[Client Name].[Client Name].[MEMBER_CAPTION]" as [Client Name],

    "[Dim Client].[Industry Name].[Industry Name].[MEMBER_CAPTION]" as [Industry Name],

    "[Dim Date].[Broadcast Year Quarter].[Broadcast Year Quarter].[MEMBER_CAPTION]" as [Broadcast Year Quarter],

    "[Dim Date].[Broadcast Year Week].[Broadcast Year Week].[MEMBER_CAPTION]" as [Broadcast Year Week],

SELECT

{

    [Measures].[Number Of Units],

    [Measures].[Gross Actual Cost]

}

DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY

CrossJoin(

    {[Dim Agency].[Agency Name].Children},

    {[Dim Buy Type].[Buy Type Name].Children},

    {[Dim Client].[Client Name].Children},

    {[Dim Client].[Industry Name].Children},

    {[Dim Date].[Broadcast Year Quarter].Children},

    {[Dim Date].[Broadcast Year Week].Children})

ON ROWS

FROM [TV Ordered and Final]

WHERE ({ [Dim Unit Size].[Size].&[30]}, { [Dim Date].[Broadcast Year].&[2013-14]});

Failed If insert [Broadcast Year]:

LOAD

    "[Measures].[Number Of Units]" as [Number Of Units],

    "[Measures].[Gross Actual Cost]" as [Gross Actual Cost],

    "[Dim Agency].[Agency Name].[Agency Name].[MEMBER_CAPTION]" as [Agency Name],

    "[Dim Buy Type].[Buy Type Name].[Buy Type Name].[MEMBER_CAPTION]" as [Buy Type Name],

    "[Dim Client].[Client Name].[Client Name].[MEMBER_CAPTION]" as [Client Name],

    "[Dim Client].[Industry Name].[Industry Name].[MEMBER_CAPTION]" as [Industry Name],

    "[Dim Date].[Broadcast Year].[Broadcast Year].[MEMBER_CAPTION]" as [Broadcast Year],

    "[Dim Date].[Broadcast Year Quarter].[Broadcast Year Quarter].[MEMBER_CAPTION]" as [Broadcast Year Quarter],

    "[Dim Date].[Broadcast Year Week].[Broadcast Year Week].[MEMBER_CAPTION]" as [Broadcast Year Week],

SELECT

{

    [Measures].[Number Of Units],

    [Measures].[Gross Actual Cost]

}

DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY

CrossJoin(

    {[Dim Agency].[Agency Name].Children},

    {[Dim Buy Type].[Buy Type Name].Children},

    {[Dim Client].[Client Name].Children},

    {[Dim Client].[Industry Name].Children},

    {[Dim Date].[Broadcast Year].Children},

    {[Dim Date].[Broadcast Year Quarter].Children},

    {[Dim Date].[Broadcast Year Week].Children})

ON ROWS

FROM [TV Ordered and Final]

WHERE ({ [Dim Unit Size].[Size].&[30]}, { [Dim Date].[Broadcast Year].&[2013-14]});


@Mod: This question should put into "Scripting" Place, but I don't know why It doesn't appear in recent topics, seems no one can see it to support.

Tags (2)
Community Browser