[ASK] extra rows when using Total function

rendiyan — Thu, 24 Nov 2016 19:27:07 GMT

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)

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)

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,

Re: [ASK] extra rows when using Total function

swuehl — Thu, 24 Nov 2016 20:05:42 GMT

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 — Fri, 25 Nov 2016 00:06:09 GMT

wow it works like charm!

thx stefan