[ASK] extra rows when using Total function

[rendiyan]

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 :

Region	Branch	Ingredient Type	Brand	Month	Sales Current Year (x1M)
West	West 1	PERMETHRIN	EXTRATINE 200 EC	Feb	62.48
West	West 1	PERMETHRIN	EXTRATINE 200 EC	Mar	18.94
West	West 2	PERMETHRIN	EXTRATINE 200 EC	Feb	24.60
West	West 2	PERMETHRIN	EXTRATINE 200 EC	Mar	38.92
West	West 3	PERMETHRIN	EXTRATINE 200 EC	Feb	15.41
West	West 3	PERMETHRIN	EXTRATINE 200 EC	Mar	15.19

=With Total Function Expression=

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

Result :

Region	Branch	Bahan Aktif	Brand	Month	Sales Current Year (x1M)	Total Sales Current Year (x1M)
JABAR	West 1	PERMETHRIN	EXTRATINE 200 EC	Feb	62.48	102.49
JABAR	West 1	PERMETHRIN	EXTRATINE 200 EC	Mar	18.94	73.05
JABAR	West 2	PERMETHRIN	EXTRATINE 200 EC	Feb	24.60	102.49
JABAR	West 2	PERMETHRIN	EXTRATINE 200 EC	Mar	38.92	73.05
JABAR	West 3	PERMETHRIN	EXTRATINE 200 EC	Feb	15.41	102.49
JABAR	West 3	PERMETHRIN	EXTRATINE 200 EC	Mar	15.19	73.05
JABAR	West 4	PERMETHRIN	EXTRATINE 200 EC	Feb	0.00	102.49
JABAR	West 4	PERMETHRIN	EXTRATINE 200 EC	Mar	0.00	73.05

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

is there anything not correct with my expression?

Thank You,

Best Regards,

[swuehl · Accepted solution]

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)

)

[rendiyan]

wow it works like charm!

thx stefan