21 Replies Latest reply: Dec 1, 2017 12:50 PM by Russell Glenn

Dimension Calculation to exclude product lines

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.

• Re: Dimension Calculation to exclude product lines

try

=aggr(

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

only ({< wProduct Line]=-{"Apple","Oranges"} >}

[wProduct Line])

)

,[wProduct Line])

• Re: Dimension Calculation to exclude product lines

This expressions errors out in expression box. I think I fixed it but did you check this through the qlik sense expression editor?

• Re: Dimension Calculation to exclude product lines

corrected it again ...

--------

I cant't test it, because I have no sample data, perhaps try:

=aggr(

if(sum({< [wProduct Line]={"*"}-{"Apple","Oranges"} >} [wIOI])>0 or sum({< [wProduct Line]={"*"}-{"Apple","Oranges"} >} wIOI)=0,

only ({< [wProduct Line]={"*"}-{"Apple","Oranges"} >}

[wProduct Line])

)

or

=aggr(

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

only ({< [wProduct Line]-={"Apple","Oranges"} >}

[wProduct Line])

)

btw

-=

differs from

={"*"} -

• Re: Dimension Calculation to exclude product lines

Thank you for the helpful formulas, I cannot seem to get it to even give me an OK symbol in the expression box though. I will keep trying.

• Re: Dimension Calculation to exclude product lines

please post a small inline sample...

• Re: Dimension Calculation to exclude product lines

I am sorry what do you mean by inline sample?

• Re: Dimension Calculation to exclude product lines

Hi Can you please provide sample QVF File

• Re: Dimension Calculation to exclude product lines

I will talk with my boss and see what he says. Thank you!

• Re: Dimension Calculation to exclude product lines

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)

• Re: Dimension Calculation to exclude product lines

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.

• Re: Dimension Calculation to exclude product lines

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

• Re: Dimension Calculation to exclude product lines

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,

• Re: Dimension Calculation to exclude product lines

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

• Re: Dimension Calculation to exclude product lines

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?

• Re: Dimension Calculation to exclude product lines

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

• Re: Dimension Calculation to exclude product lines

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

• Re: Dimension Calculation to exclude product lines

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

• Re: Dimension Calculation to exclude product lines

Don't really know... unless you can share some raw data... I won't really know...

• Re: Dimension Calculation to exclude product lines

I will push to see what we can do on that front. Thanks for your help Sunny.

• Re: Dimension Calculation to exclude product lines

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

Thanks,