Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rglenn0780
Contributor

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.

Tags (1)
21 Replies
roharoha
Valued Contributor III

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])

rglenn0780
Contributor

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?

roharoha
Valued Contributor III

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

={"*"} -

rglenn0780
Contributor

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.

roharoha
Valued Contributor III

Re: Dimension Calculation to exclude product lines

please post a small inline sample...

rglenn0780
Contributor

Re: Dimension Calculation to exclude product lines

I am sorry what do you mean by inline sample?

h_prakash
Contributor

Re: Dimension Calculation to exclude product lines

Hi Can you please provide sample QVF File

rglenn0780
Contributor

Re: Dimension Calculation to exclude product lines

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

roharoha
Valued Contributor III

Re: Dimension Calculation to exclude product lines

have a look here:

Scramble Data

Community Browser