How to make my column = stock amt turn on show null value ?

[paulyeo11]

Hi All

My Table 1 for YearMonth i not tick on Show null value.

My Table 2 for YearMonth i have tick on Show null value.

Table 1 50010 Amount is wrong and Table 2 50010 Amount is correct , because YearMonth i have tick on show null value.

My question is Table to for stock amt 1,763327 is wrong , it should be display as 1,452,716 ,May i know how to make it not show null value ?

In another word , how to make Table 1 and 2 Column = stock amt display as 1,452,716 ?

Paul

[paulyeo11]

my qvf

[sunny_talwar]

Seems to be working with the if statement instead of using set analysis. Although I am not sure why that case is

Money(RangeSum(

Above(RangeSum(Above(Sum({<year, month>} If(GL_CODE=50010, [Amount]*1)), 0, RowNo()))),

-Sum(TOTAL If(GL_CODE=50010, Aggr(Sum({<year, month>} If(GL_CODE=50010, [Amount]*1)), YearMonth))))

, $(vMoneyFormatK_GL))

[paulyeo11]

Hi sunny

I know the issue why , because last week when I ask you , I use the simple file mainly consist of P&L table . Since the file the filed name Amount , don't have null value . So your SET expression work fine.

But when I apply your SET expression into my actual QV Doc , which include many other table , and many other table also have same field name Amount.

This way cause the SET expression not working fine.

In order for my solve the problem , I need to change the field name from Amount to Amount_ . This will avoid null value.

Not sure you can help a distinct in the SET expression ? As if SET expression is work , I hope that in future I can allow user to select particular date it can display the stock amount.

Paul

Sent from my iPhone

[sunny_talwar]

So you are trying to pick Amount from a specific table?

[paulyeo11]

Yes you are right. I only want to pick the Amount from my GL Table . As my competitor sales table also have the same field name Amount.

Paul

Sent from my iPhone

[sunny_talwar]

Not sure I can recommend a solution since your data model is very complicated and finding the issue would be like looking for a needle in a haystack .

[paulyeo11]

Yes you are right , no wat you can trouble shoot by looking as my data model. I think it is ok , I can modify the field name the new name and it will solve the issue. Thank you very much .

By the way my next question is once I can remove the null value , do you think it is possible to make the SET expression more dynamic to display the stock amount by click on year = 2015 and month = March , will it be possible to display 2015 March stock amount ?

Meaning base on last week your SET expression , on computer monthly stock amount by month. Can I get particular month stock amount ? So that I don't need create a table with every month one expression , like stock for 1 yr ago and 1 month ago approach.

Hope you can share with me on this .

Paul

Paul

Sent from my iPhone