6 Replies Latest reply: Jun 11, 2013 4:17 AM by Kevin Lei RSS

    Cannot use data from different sources in one formula

      Hi,

      I meet the following issue: the main part of the source data of my Qlikview report is retrieved from the CRM database
      stored in SQL 2K, while some others are given by Excel file, like sales milestones; and I can't use the data from both
      sources together in one formula, for instance: the payment collection milestone is calculated as: (the cumulative amount
      of 'SOLD' contracts till current week) X (a given percentage value); the 1st value can be calculated with the data got from
      SQL database, and the 2nd one is listed in the Excel file, like:

      Week 1 10%
      Week 2 12.3%
      Week 3 14%
      Week 4 15.2%
      ...
      ...

      Week 30 98.1%
      Week 31 100%

      These data can be loaded into Qlikview wiht no issue through separate script block, but when I tried to use them into
      one formula, it always get a Zero.


      Is anybody who can tell me how resolve this? I have been stuck here for a long time, and the deadline is coming...

      Thanks anyway!

        • Re: Cannot use data from different sources in one formula
          Stefan Wühl

          You need to post some more information about your data model, dimensions and expressions used in your chart.

           

          I assume you are using a date / week dimension, that is linked to your SQL table with the SOLD contracts.

          I also assume your second table is not linked to that dimension, so QV won't know which percentage to use for the calculation, thus it will return NULL (that might lead to a zero in further calculation).

            • Re: Cannot use data from different sources in one formula

              QVStructure.jpg

              Thanks!

               

              I think you are right. Please refer to the above screen show of the table structure of my report -- the main data table (the bigger one in the right) which contain the data of SOLD amount does link with the data table retrieve from Excel (the smaller one in the left) through column 'WeeksOut'; however, in the formula of paymant dashboard, I have to use column 'wks' (the column in the smallest table shown in the bottom-left corner) as the dimension of the dashboard for other calculations in same dashboard are rely on it, and this is a inline table, and the values and set like following:

               

              0

              5

              10

              15

              20

              25

              30

              35

               

              So there any suggestion that you can give me about how to solve this problem, by either

              1. change the loading methods of the source data

              2. or, change the settings, like dimension column, in the dashboard I am working on

              3. or, change the formula I use for payment milestone calculation,shown as following

              {

              if (wks>=WeeksOutfromToday,

              Sum(if(WeeksOut>=wks and ShowYear=ShowYear and Show=Show and Revenue>0 and (OrderlineStatus='SOLD' or OrderlineStatus='Return Cash'),Revenue,0)) * CollectionMileStone / 1000

              )

              }

              or any other way?

               

              Thanks anyway!