Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
rendiyan
Partner
Partner

[ASK] extra rows when using Total function

Dear Qlik Master,

i have an issue with result in one of my chart.

When i create an expression without total function, the row comes out normally.

But when i add an expression with total function, there are some extras row comes out.

=Without Total=

Expression

sum(

{<  FLAG_TRANS={'Item_Sales_Detail'},  Year={'$(=max(Year))'}>}

ITEMBASEAMOUNT)

Result :

RegionBranchIngredient TypeBrandMonthSales Current Year (x1M)
WestWest 1PERMETHRINEXTRATINE 200 ECFeb62.48
WestWest 1PERMETHRINEXTRATINE 200 ECMar18.94
WestWest 2PERMETHRINEXTRATINE 200 ECFeb24.60
WestWest 2PERMETHRINEXTRATINE 200 ECMar38.92
WestWest 3PERMETHRINEXTRATINE 200 ECFeb15.41
WestWest 3PERMETHRINEXTRATINE 200 ECMar15.19

=With Total Function Expression=

Expression

sum(total<Year,Month,Year,Month,Brand>

  {<FLAG_TRANS={'Item_Sales_Detail'},Year={'$(=max(Year))'}>}

  ITEMBASEAMOUNT)

Result :

RegionBranchBahan AktifBrandMonthSales Current Year (x1M)Total Sales Current Year (x1M)
JABARWest 1PERMETHRINEXTRATINE 200 ECFeb62.48102.49
JABARWest 1PERMETHRINEXTRATINE 200 ECMar18.9473.05
JABARWest 2PERMETHRINEXTRATINE 200 ECFeb24.60102.49
JABARWest 2PERMETHRINEXTRATINE 200 ECMar38.9273.05
JABARWest 3PERMETHRINEXTRATINE 200 ECFeb15.41102.49
JABARWest 3PERMETHRINEXTRATINE 200 ECMar15.1973.05
JABARWest 4PERMETHRINEXTRATINE 200 ECFeb0.00102.49
JABARWest 4PERMETHRINEXTRATINE 200 ECMar0.0073.05

Expected result is that Branch "West 4" not shows up.

is there anything not correct with my expression?

Thank You,

Best Regards,

1 Solution

Accepted Solutions
swuehl
MVP
MVP

To suppress the rows with zero in your first expression, just check for the results of the first expression:

If(

          sum({< FLAG_TRANS={'Item_Sales_Detail'}, Year={'$(=max(Year))'}>} ITEMBASEAMOUNT),

          sum(total<Year,Month,Year,Month,Brand>   {<FLAG_TRANS={'Item_Sales_Detail'},Year={'$(=max(Year))'}>}       ITEMBASEAMOUNT)

)

View solution in original post

2 Replies
swuehl
MVP
MVP

To suppress the rows with zero in your first expression, just check for the results of the first expression:

If(

          sum({< FLAG_TRANS={'Item_Sales_Detail'}, Year={'$(=max(Year))'}>} ITEMBASEAMOUNT),

          sum(total<Year,Month,Year,Month,Brand>   {<FLAG_TRANS={'Item_Sales_Detail'},Year={'$(=max(Year))'}>}       ITEMBASEAMOUNT)

)

View solution in original post

rendiyan
Partner
Partner
Author

wow it works like charm!

thx stefan