My Qliksense charts don't populate with numbers (SAP-BW MDX cubes)

[quriouss]

I downloaded the free version of Qliksense to try it out, but I'm finding it tough-going ...  I'm running it on top of an SAP-BW system, which is tough in itself, but I finally worked out how to connect the two together.

I am getting a nice, simple data set pulled through, but if I try to create a chart from the data, the chart is not populating with numbers.  It *looks* like it thinks the measures are text, and I can't think of a way to force it to see the measures as numbers.

My MDX expression is;

SELECT

NON EMPTY { [Measures].[Sales],[Measures].[Constant]} ON COLUMNS NON EMPTY { SHOP_ID} ON ROWS FROM [SALE_RESULTS]

[Sales] is in USD (but I have tried using the SAP function NODIM(Sales) in the BEx query to give a unit-less number

[Constant] is just a static number in the BEx query (to force it to be an integer.

Neither approach works.

If I add the data as a table, I can see they look like numbers (they display with a decimal point  and two decimal places "X.00") but if I put the measures onto a chart, all the values don't display (or display as zero, it's hard to tell).

Any ideas?  Anybody?

Is it something to be fixed in the MDX (something like a function to force a number-format) or in SAP/BW/BEx?

Many thanks,

[JonnyPoole]

Go to the Data model viewer and lets preview what the numbers look like in Sense. Here is a screenshot of where to go... post a screenshot of what the numbers look like .  Also would be helpful to see what they look like in the UI as a table object or chart. 

[quriouss]

So here is my data in the Data Model Viewer (dimension names blurred, because ...)

And here is the same data put into a table (two columns; One Dimension and One Measure);

And finally, if I add the same data to a bar chart;

[JonnyPoole]

Its definitely a little odd.. .are you  using sum(Measures) as the measure in the tabular output as well ?

Would you be willing to post the .QVF file ?  There are multiple things i'd like to look at.

If you can't post the QVF, can you post the following ?

1. The script from the load editor

2. a screenshot of the measure expression used in the table AND in the bar chart (please use a new bar chart to ensure all settings are default)

[quriouss]

The connection script is;

LIB CONNECT TO 'SYSTEM_NAME';

SELECT

          { [Measures].[DBIVFRETGGSJ67J75X], [Measures].[D65FGHICFRAXZ] } ON COLUMNS

          NON EMPTY ( STORE_NUMBER } ON ROWS

FROM [INFO_PROVIDER\QUERY_NAME]

(I've tried it with and without all the standard "SET=" statements; it makes no difference).

In the charts and the table the values to be plotted are SUM([[Measures].[DBIVFRETGGSJ67J75X]]).  If I change the expression to just a value, then the plot works, but if I d something like "3+SUM([[Measures].[Amount]])" then it goes back to NULL - i.e. the SUM(Measures) is not evaluating as a number.

If I go to edit the expression, the editor shows an error, but all the brackets match (and it is the default-generated expression).

And finally, if I plot the values onto a line chart, then change the option to "show missing values as ..." to ZERO, then I can get a line plot of zeroes.

I suspect my problem is with the data extract script.  I think there are some NULL values coming in somewhere, which is confusing Qliksense, but I don't know where to fix this.

[JonnyPoole · Accepted solution]

I agree. I think the issue is that the field names have square brackets in them , or something along those lines

In Qlik Sense, you would normally have to wrap the field in double quotes in order for it to load properly. I suspect with the [].[] syntax from SAP its getting very confused.

LOAD

  "[Region]" as Region,

     "[Sales]" as Sales

FROM

<source>

Here is what i suggest. If you uses the wizard in qlik sense to generate the select, try checking the 'include load statement' in the bottom left below and qlik sense should take care of the names.

If you are NOT seeing that option, or don't have that option to begin with, manually add a 'preceding load' in from of the SQL .  I don't have your app to test, but look below and notice how i'm explicitly loading the field names in the preceding load . I can do this with all or just a select few that are returned from the MDX. You would want to load all the fields by name using the "" syntax and also change the 'AliasName' values to be business friendly field names for you qlik data model.

let me know how that goes.

IB CONNECT TO 'SYSTEM_NAME';

Load

     "[Measures].[DBIVFRETGGSJ67J75X]" as AliasName,

     " [Measures].[D65FGHICFRAXZ]" as OtherAliasName

     ;

SELECT

          { [Measures].[DBIVFRETGGSJ67J75X], [Measures].[D65FGHICFRAXZ] } ON COLUMNS

          NON EMPTY ( STORE_NUMBER } ON ROWS

FROM [INFO_PROVIDER\QUERY_NAME]

[quriouss]

So it appears with SAP-BW you don't get to use the wizard (it's stubbornly blank), but the manual approach of adding a pre-load statement to the script seems to work.

I'm getting some sensible numbers now, but ...  one small point...  I've lost the ROW dimension.

If I add a "pre-load" statement in the script, do I have to provide an alias for *everything*?  (that seems a small price to pay!)

I think I'm getting somewhere thanks to your help.

[JonnyPoole]

You can use a * to load all the fields 'as is' but then you can't customize the field names so i'm afraid the answer is yes.

Two things though:

1. Its upfront work that doesn't need to be repeated

2. you can 'store' the resulting table with all aliases to disk as a QVD file and reuse it as a data source for other qlik sense applications.  So you  MAY never have to do this again for any Qlik apps that leverage at least these fields.

[quriouss]

Thanks, and I'll leave a note here in case anyone else (including me) comes here in the future;

This works;

Load

     "[Measures].[LongSAPTechnicalName]" as AliasName,

     "[CHARACTERISTIC].[LEVEL01].[MEMBER_CAPTION]" as Store_Location;

SELECT

     { [Measures].[LongSAPTechnicalName] } ON COLUMNS,

NON EMPTY { [CHARACTERISTIC].[LEVEL01] } ON ROWS

FROM [BW_CUBE/QUERY_NAME];