excluded "Others" in pivot table (Totals)

[Anonymous]

hi,

i only want to see 'Others' value when i have at least one product for my segment.

if i have only "others" for segment i don't want to show that values.

in attach , qvw file and excel for example.

can you help me ?

[vinieme12]

Your other products only appear when Marketing. = null

So a a condition to sum only w market is not null

Sum({<Market={'$(=len(Market)>0)'}>}qty)

[sunny_talwar]

Like this?

If(Count(TOTAL <TaskName, MarketName, MarketCategoryName, MarketSegmentName> DISTINCT {<TaskProductType = {1}>} ProductName), Sum(Quantity))

[Anonymous]

Thanks for the help but I tested your solution and it did not work.

[Anonymous]

I think the solution is here. Almost worked. Only when I shrink to the first level does the total not match.

I created a new application with different values to make the error perceptible.

but in your example you can shrink Task 'A' and Total must be 244 (234+10) not 545.

can you help me?

thanks

Ricardo

[sunny_talwar · Accepted solution]

You just need to do Sum(Aggr()) here

=Sum(Aggr(If(Count(TOTAL <TaskName, MarketName, MarketCategoryName, MarketSegmentName> DISTINCT {<TaskProductType = {1}>} ProductName), Sum(Quantity)), TaskName, MarketName, MarketCategoryName, MarketSegmentName, ProductName))

[vinieme12]

Ok i see you have a calculated dimension, just add the dimension condition to you Expression in set analysis

sum({<MarketName = {"=sum(TaskProductType)>0"}>}Quantity)

using expression'

= sum({<MarketName = {"=sum(TaskProductType)>0"}>}Quantity)

[sunny_talwar]

The only issue I foresee is that if there are more than one MarketCategory or MarketSegment within a single MarketName. I think with multiple dimensions in play, I would not rely on set analysis, unless I can create a key of these 5 fields in the script to do this search string.

[Anonymous]

Great . Both solutions work. but sunny T was Faster

[sunny_talwar]

If in your actual scenario, both the expressions are working. I would go by vinieme12‌'s response.