I come from a Microsoft background but have made a couple of forays into the world of Qlikview. I noticed that just like MDX is used to enhance reports and cubes when working in the Microsoft BI environment, Qlikview provides set analysis expressions as a tool for sorting and querying subsets of data within a Qlikview document. The set analysis syntax is quite different from MDX so I thought a little comparison using the AdventureWorks data structure might be worthwhile.
In MDX a query scoped member or calculated measure totaling the sales amount for male customers would look like this:
sum([DimCustomer].[Gender].&[M],[Measures].[Sales Amount])
Translated into a Qlikview set analysis expression we end up with the following:
=sum({$<Customer.Gender = {'M'}>} OrderDetails.[Sales Amount])
An MDX scoped member or calculated measure totaling the sales for all male customers in Germany and France would be defined using a crossjoin (note the * operator):
sum({[DimSalesTerritory].[SalesTerritoryCountry].&[France],
[DimSalesTerritory].[SalesTerritoryCountry].&[Germany]} *
[DimCustomer].[Gender].&[M], [Measures].[Sales Amount])
Using Qlikview set analysis the above would look like this:
=sum({$ < Customer.Gender = {'M'},
SalesTerritory.SalesTerritoryCountry =
{'Germany','France'}>} OrderDetails.SalesAmount)
A total excluding France and Germany would look like this in MDX:
sum(except([DimSalesTerritory].[SalesTerritoryCountry].children,
{[DimSalesTerritory].[SalesTerritoryCountry].&[France],
[DimSalesTerritory].[SalesTerritoryCountry].&[Germany]},
[Measures].[Sales Amount])
using Qlikview set analysis we get this:
=sum({$ <SalesTerritory.SalesTerritoryCountry =
-{'Germany','France'} >} } OrderDetails.SalesAmount)
An mdx query calculating the two years with the highest sales from the AdventureWorks cube would look like this:
SELECT
sum(TOPCOUNT([DimDate].[CalendarYear].[CalendarYear], 2,
[Measures].[Sales Amount]),[Measures].[Sales Amount])
ON 0
from [AWModel]
In Qlikview you would create a chart displaying Sales Amount by Calendar Year and then select Properties–>Dimension Limits and check the ”Select which values are displayed” check box so you can set the chart to display only the two largest values.
Or you can create a list box for Calendar Year next to your chart and type the following set analysis expression into the search box:
=rank(sum(OrderDetails.SalesAmount))<=2
and press enter. The two years with the largest sales are now selected by the expression you just created.
I think it is quite safe to assume that one of the reasons it is quite time consuming to replace an OLAP-cube with a Qlikview document or vice versa, are the syntactical differences.
Notes: I have used the sum function on the MDX-statements for the sake of clarity. It is actually not needed since the property of the measure itself is set to sum in the SSAS cube definition. If we substitute the ”$” for ’1′, our selections will be independent of any selections in the Qlikview document.
