topic Re: Set analysis filter on function values within a range in QlikView

Set analysis filter on function values within a range

Tue, 03 Mar 2015 20:10:36 GMT

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:

customer	margin (gross)	amount
a	10	20
a	2	22
b	-7	51
c	5	15
c	37	37
c	14	28
d	6	12
d	-10	19

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

negative average margin percentage ie. customer b has an average -7/51
average margin perc. between 0% and 20%
average margin perc. between 20% and 50%
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?

Re: Set analysis filter on function values within a range

maxgro — Tue, 03 Mar 2015 20:27:01 GMT

maybe this

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

EDIT: PFA

Re: Set analysis filter on function values within a range

martinpohl — Tue, 03 Mar 2015 20:33:40 GMT

see attached

Regards

Re: Set analysis filter on function values within a range

Tue, 03 Mar 2015 21:16:16 GMT

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...

Re: Set analysis filter on function values within a range

Tue, 03 Mar 2015 21:20:23 GMT

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.

Re: Set analysis filter on function values within a range

Tue, 03 Mar 2015 21:41:46 GMT

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?

Re: Set analysis filter on function values within a range

Anonymous — Tue, 03 Mar 2015 21:45:12 GMT

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