Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

paulyeo11
Valued Contributor II

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

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.

1 Solution

Accepted Solutions

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

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..

Capture.JPG

5 Replies
paulyeo11
Valued Contributor II

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

my QVW

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

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..

Capture.JPG

paulyeo11
Valued Contributor II

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

Hi Manish

Very smart move. Thank you.

Paul

paulyeo11
Valued Contributor II

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

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

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

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