Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis filter on function values within a range

Hello, I am trying to create a bar chart where the bars are the sum of the sales over a subset of entries selected based on the average margin.  For example, the data points used are like:

customermargin (gross)amount
a1020
a222
b-751
c515
c3737
c1428
d612
d-1019

I want to find the amount summed over the customers with the following attributes:

  1. negative average margin percentage ie. customer b has an average -7/51
  2. average margin perc. between 0% and 20%
  3. average margin perc. between 20% and 50%
  4. average margin perc. >50%

I have 1. and 4. ie.:

sum({<customer={"=avg(margin/amount)<=0"}>} amount)

but all attempts to create a range have failed ie.:

sum({1<customer={"=avg(margin/amount)>=.2"},customer={"=avg(margin/amount)<=.50"}>} amount)

sum({1<customer={"=avg(margin/amount)>=.2"} AND customer={"=avg(margin/amount)<=.50"}>} amount)

sum({1<customer={"avg(margin/amount)>=.2, avg(margin/amount)<=.50"}>} amount)


and many more but I've lost track and am obviously missing one particular version...


Bonus: how do I create a pie chart with this info without coding the margin range into the load statement?

1 Solution

Accepted Solutions
maxgro
MVP
MVP

maybe this

sum({$<customer={"=avg(margin/amount)>=.0"}*{"=avg(margin/amount)<=.2"}>} amount)

EDIT: PFA

1.jpg

View solution in original post

6 Replies
maxgro
MVP
MVP

maybe this

sum({$<customer={"=avg(margin/amount)>=.0"}*{"=avg(margin/amount)<=.2"}>} amount)

EDIT: PFA

1.jpg

martinpohl
Partner - Master
Partner - Master

see attached

Regards

Not applicable
Author

Hello, thanks-this is nice and very close but I need to find customers with average margins in the given buckets as opposed to counting distinct instances.  I am trying to modify with the aggr function-I will post above...

Not applicable
Author

I have now also tried aggregated expressions:

sum(aggr(if(avg(margin/amount)>.2 and avg(margin/amount)<=.45,customer), amount))

which produces wildly large sums.  I just don't know how to approach this-calculated dimension, expression, aggregation...can't seem to get anything to return appropriate amounts.

Not applicable
Author

Thanks Massimo! This 'seems' to work-at least my numbers add up now...I used Year (I have it in my data set) as the dimension and then used your expressions.  One question so I can be comfortable with what this is doing:

What does the '*' between sets mean?

Anonymous
Not applicable
Author

* returns the set of records that only belong to the overlapping data from the first and second sets (similar to an INTERSECT in SQL)