topic Cannot use data from different sources in one formula in QlikView

Cannot use data from different sources in one formula

Sat, 08 Jun 2013 07:30:18 GMT

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

swuehl — Sat, 08 Jun 2013 09:48:49 GMT

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

Sun, 09 Jun 2013 02:52:17 GMT

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:

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!

Re: Cannot use data from different sources in one formula

swuehl — Sun, 09 Jun 2013 13:24:14 GMT

It's hard to give an advice without knowing your full setting and requirements.

From what I see, I would check wks >= WeeksOutfromToday ,i.e. double check WeeksOutfromToday (I can't see where this is coming from).

Also your conditions ShowYear = ShowYear and Show=Show don't seem meaníngful to me.

I would start with creating an expression as simple as possible, then adding more complexity one after another as needed (and only if it's needed!).

Good luck,

Stefan

Re: Cannot use data from different sources in one formula

Tue, 11 Jun 2013 08:16:00 GMT

Thank you Stefan for your kindly help! I will send you a more complete sample file later.

Re: Cannot use data from different sources in one formula

Tue, 11 Jun 2013 08:16:29 GMT

Thank you Stefan for your kindly help! I will send you a more complete sample file later.

Re: Cannot use data from different sources in one formula

Tue, 11 Jun 2013 08:17:23 GMT

Thank you Stefan for your kindly help! I will send you a more complete sample file later.