8 Replies Latest reply: Nov 25, 2014 1:03 PM by Simon Hogg RSS

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

    Simon Hogg

      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,

        • Re: My Qliksense charts don't populate with numbers (SAP-BW MDX cubes)
          Jonathan Poole

          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. 

           

          Untitled.png

            • Re: My Qliksense charts don't populate with numbers (SAP-BW MDX cubes)
              Simon Hogg

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

              QlikSense Nov 2014.png

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

              QlikSense Nov 2014_2.png

               

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

              QlikSense Nov 2014_3.png

                • Re: My Qliksense charts don't populate with numbers (SAP-BW MDX cubes)
                  Jonathan Poole

                  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)

                    • Re: My Qliksense charts don't populate with numbers (SAP-BW MDX cubes)
                      Simon Hogg

                      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).

                       

                      QlikSense Nov 2014_4.png

                       

                      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.

                        • Re: My Qliksense charts don't populate with numbers (SAP-BW MDX cubes)
                          Jonathan Poole

                          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.

                           

                          Untitled.png

                           

                          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]