Why i insert sale={'0'} into my SET expression , it unable filter off those row with zero ?

[paulyeo11]

TABLE 1 EXPRESSION IS OKAY :-

if(

Sum({$<year = {$(=Max(year)-1)}, month = {"<=$(=Max({<year={$(=Max(year))}, sales = {'*'}>} month))"}>}sales/Rate)

>=

Sum({$<year = {$(=Max(year)-0)}, month = {"<=$(=Max({<year={$(=Max(year))}, sales = {'*'}>} month))"}>}sales/Rate)

,

Sum({$<year = {$(=Max(year)-0)}, month = {"<=$(=Max({<year={$(=Max(year))}, sales = {'*'}>} month))"}>}sales/Rate)

)

IN ORDER TO FILTER OFF ZERO ROW , I ADD sale={'0'} TO ABOVE EXPRESSION :-

if(

Sum({$<year = {$(=Max(year)-1)}, month = {"<=$(=Max({<year={$(=Max(year))}, sales = {'*'},sale={'0'}>} month))"}>}sales/Rate)

>=

Sum({$<year = {$(=Max(year)-0)}, month = {"<=$(=Max({<year={$(=Max(year))}, sales = {'*'},sale={'0'}>} month))"}>}sales/Rate)

,

Sum({$<year = {$(=Max(year)-0)}, month = {"<=$(=Max({<year={$(=Max(year))}, sales = {'*'},sale={'0'}>} month))"}>}sales/Rate)

)

But it never work. See Table 2., Now it display total 18 company , i need it display only 1 to 3 row.

[paulyeo11]

my QVW

[MK_QSL · Accepted solution]

Change your first expression as below

IF(Column(2), if(

Sum({$<year = {$(=Max(year)-0)}, month = {"<=$(=Max({<year={$(=Max(year))}, sales = {'*'}>} month))"}>}sales/Rate)

<=

Sum({$<year = {$(=Max(year)-1)}, month = {"<=$(=Max({<year={$(=Max(year))}, sales = {'*'}>} month))"}>}sales/Rate)

,

Sum({$<year = {$(=Max(year)-1)}, month = {"<=$(=Max({<year={$(=Max(year))}, sales = {'*'}>} month))"}>}sales/Rate)

))

You will get result like below..

[paulyeo11]

Hi Manish

Very smart move. Thank you.

Paul

[paulyeo11]

Hi Sir

Can you share with me what is the meaning for below script ?

IF(Column(2), if(

As i ready don't know where to start to modify your script , so that i can list row 8 till 27 , as i also need the list of customer stop buiuying .

Paul

[MK_QSL]

If(Column(2) means 2nd expression... if value is other than 0 then only 1st expression will give you value otherwise null.