Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Discussion Board for collaboration related to QlikView App Development.

Announcements

QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: **REGISTER NOW!**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Set analysis filter on function values within a ra...

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Not applicable

2015-03-03
03:10 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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:

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?

1,297 Views

1 Solution

Accepted Solutions

maxgro

MVP

2015-03-03
03:27 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

maybe this

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

EDIT: PFA

966 Views

6 Replies

maxgro

MVP

2015-03-03
03:27 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

maybe this

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

EDIT: PFA

967 Views

martinpohl

Partner - Master

2015-03-03
03:33 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

see attached

Regards

966 Views

Not applicable

2015-03-03
04:16 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

966 Views

Not applicable

2015-03-03
04:20 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

966 Views

Not applicable

2015-03-03
04:41 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?

966 Views

michael_gardner

Creator III

2015-03-03
04:45 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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