Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

paulyeo11
Valued Contributor II

How to list sales increase and new customer from sales increase list ?

Hi All

After you successfully help me on sales decline listing to break into 2 table.

Now I have another list , which is sales increase listing table 1 Row 1 to 6 into Table 2

when i try to follow the same approach , I manage to display 3 company only . i miss out 3 company. not sure why ?

For new customer listing. Table 3., i try to filter Row 6 till 24 from Table 1 , i try to follow the same approach , but i still stuggle with it. Hope you can advise me.

1 Solution

Accepted Solutions
MVP
MVP

Re: How to list sales increase and new customer from sales increase list ?

1st Table

1st Expression

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)

)

2nd Expression

If(Column(1),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))

)

3rd Expression

=If(Column(1),if(fabs(column(1))=0,1,column(2) / fabs(column(1))-1))

4th Expression

If(Not IsNull([YTD Variance]),1,0)

2nd Table

1st Expression

If(

  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)

  )

=0,

  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)

  ))

2nd Expression

If(Column(1) = 0,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))

)

3rd Expression

=if(fabs(column(1))=0,1,column(2) / fabs(column(1))-1)

4th Expression

If(Not IsNull([YTD Variance]),1,0)

4 Replies
paulyeo11
Valued Contributor II

Re: How to list sales increase and new customer from sales increase list ?

my QVW

paulyeo11
Valued Contributor II

Re: How to list sales increase and new customer from sales increase list ?

Hi Manish

sales up list.png

Above is the report i expected for sales increase list.

Below is the report i expected for sales increase from new customer.

new customer list.png

MVP
MVP

Re: How to list sales increase and new customer from sales increase list ?

1st Table

1st Expression

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)

)

2nd Expression

If(Column(1),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))

)

3rd Expression

=If(Column(1),if(fabs(column(1))=0,1,column(2) / fabs(column(1))-1))

4th Expression

If(Not IsNull([YTD Variance]),1,0)

2nd Table

1st Expression

If(

  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)

  )

=0,

  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)

  ))

2nd Expression

If(Column(1) = 0,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))

)

3rd Expression

=if(fabs(column(1))=0,1,column(2) / fabs(column(1))-1)

4th Expression

If(Not IsNull([YTD Variance]),1,0)

paulyeo11
Valued Contributor II

Re: How to list sales increase and new customer from sales increase list ?

Hi Manish

Now working fine .

1) I make many mistake on 0 or 1 and > and <

2) Lucky you give me the expression , in case you only give me QVW , i don't think i am able to download as now my internet slow.

Great that now it every thing okay. are you work and live in Singapore ?

Paul

Community Browser