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.
