Dimension Calculation to exclude product lines

[Anonymous]

Hello,

We have a certain dimension calculation that displays the correct amount of product lines for our revenue calculation. We have been tasked with excluding certain product lines from the data in qlik and are stuggling with combining the two formulas.

=aggr(if(sum(wRevenue)>0 or sum(wRevenue)=0,
[wProduct Line]=-{"Apple","Oranges"},[wProduct Line], ))

In essence we want to exclude apples and oranges but keep the aggr function working. Original agg function was:

=aggr(if(sum([wIOI])>0 or sum(wIOI)=0, [wProduct Line]),[wProduct Line])

Thank you so much for your time.

[sunny_talwar]

May be you need this

Dimension

=Aggr(If(Sum({<[wProduct Line]=-{"Apple","Oranges"}>}[wIOI])>0 or Sum({<[wProduct Line]=-{"Apple","Oranges"}>}wIOI)=0, [wProduct Line]), [wProduct Line])

Expression (Assuming it was Sum(Measure))

Sum({<[wProduct Line]=-{"Apple","Oranges"}>}Measure)

[Anonymous]

Hi Sunny, This works and does bring in numbers for top 10 revenue bar chart display. Where we are struggling is we cannot seem to bring in product lines that have $0 in for revenue. We have tried setting the Dimension field to <=0 and nothing seems to display. When we select to display zero values, we get additional product lines that have nothing filled in. (They did not put an amount into our source file) Some product lines actually put in $0, so we still want to display the ones that put a $0 value versus the ones that put nothing in.

Our current dimension field is:

=Aggr(If(Sum({<[wProduct Line]=-{"Apples","Oranges"}>}[wRevenue])>0

or Sum({<[wProduct Line]=-{"Apples","Oranges"}>}[wRevenue])<=0

, [wProduct Line]),[wProduct Line])

And current measure is setup to default to current month. In this case we are using a Maxstring formula also from the community.

Revenue Measure:

sum({<wMonth={"$(=MaxString(Date#(wMonth,'MMM')))"} ,  [wProduct Line]-={"Apples","Oranges"}>}wRevenue)

The top 10 revenue display works with what you provided. It always seems the bottom 10 is the pesky side of it.

Unfortunately, my boss would need to prepare the scrambled data sample, and if this is something that can be done, I will definitely provide one.

[Anonymous]

Essentially, we are attempting to exclude the product lines that we are trying to exclude and include the zero revenue product lines.

Thanks,

[sunny_talwar]

I am not entirely sure if I understand you... you want to display 0 product line in top 10?

[Anonymous]

Hi Sunny,

Sorry for the confusion! We have a couple bar graphs and the problem is evident in the bottom 10 revenue lines. Excluding the product lines we listed, but also brining in the product lines that have literally a $0 in for Revenue.

Thanks,

[sunny_talwar]

If they are true 0 and not missing or null, I don't see why they should not show up... just make sure that you have checked Include Zero Values under Add-ons -> Data Handling

[Anonymous]

We do check the include zeros, but in the process we also get the ones we wanted to exclude pulled in as zeros. Even though they are listed in that exclusion list where the Apples and Oranges are in the function,

Maybe it is a problem with the expressions?

[sunny_talwar]

May be change the dimension to this

=Aggr(If(Sum({<[wProduct Line]=-{"Apple","Oranges"}>}[wIOI])>0 or Sum({<[wProduct Line]=-{"Apple","Oranges"}>}wIOI) = 0 and not Match([wProduct Line], 'Apple', 'Oranges'), [wProduct Line]), [wProduct Line])

now uncheck Include null values under Data -> Dimension, but keep Include Zero values checked under Add-ons -> Data Handling

[Anonymous]

Hi Sunny,

I think we are close.

sum({<wMonth={"$(=MaxString(Date#(wMonth,'MMM')))"}>}wRevenue) is my measure

and my dimension is

=Aggr(If(Sum({<[wProduct Line]=-{"Apple","Oranges"}>}[wRevenue Sum({<[wProduct Line]=-{"Apple","Oranges"}>}wRevenue and not Match([wProduct Line], 'Apple', 'Oranges'), [wProduct Line]), [wProduct Line])

but it is still not displaying the zero value product lines.

I can see them in the source file as having zero revenue, but the bar chart begins at the smallest one over zero.

Thanks

[Anonymous]

I tried the equal to functions as well and that does not seem to have an effect.