Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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]});

Tags (2)
1 Reply
Not applicable

Re: How to load dimension with filter itself in MDX Query Generator

I just posted this topic, but why can't see it appear in recent topics section?

Community Browser