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