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: 
Anonymous
Not applicable

Set Analysis on aggr totals

I'm trying to see if their is a Set Analysis way to solve the following problem.  I used an if statement.

Problem

For each "Customer", show the "Total Revenue" if they have order at least 100 units (Quantity) in all their orders combined.  There are multiple orders per customer and I only care if they exceed 100 combined.

Current Solution

if(aggr(sum(Quantity), Customer) > 100, Total Revenue, 0)

Is there a set analysis way to do this?  Should it be done with set analysis?

1 Solution

Accepted Solutions
luismadriz
Specialist
Specialist

Hi,

Do you want to try this for Revenue: =sum( {$ <Customer={"=sum(Quantity)>100"} >} Revenue)

Please let me know,

Cheers,

Luis

View solution in original post

6 Replies
luismadriz
Specialist
Specialist

Hi,

Do you want to try this for Revenue: =sum( {$ <Customer={"=sum(Quantity)>100"} >} Revenue)

Please let me know,

Cheers,

Luis

Anonymous
Not applicable
Author

Good solution to the problem above. However, in analyzing the data, there was a wrinkle.  When I grouped by Region, the filter seemed to apply to the entire data set, rather than being limited to the Customer/Region.

How would I solve the following where Region is added to the aggregation using Set Analysis?


if(aggr(sum(Quantity), Customer, Region) > 100, Total Revenue, 0)

wizardo
Creator III
Creator III

hmm, i might be mistaken but i'm not sure that its possible,

set analysis is calculated once per the entire object so it is unaware of the dimensions in it, only after the set analysis calculates which rows are possible for the chart, then the object calculates in accordance with the dimension values.

so you will need some form of aggragation in order to make the sum of 100 the limit of each customer and region.

probably the IF is only option

Wizardo

sunny_talwar

You can make a new field in the script like this

LOAD Customer,

     Region,

     AutoNumber(Customer&Region) as CustomerRegionKey

     ...

FROM ....;

and then this

Sum({<CustomerRegionKey = {"=Sum(Quantity) > 100"}>}[Total Revenue])

luismadriz
Specialist
Specialist

Hi,

I'm back now. Hope you guys had a nice break!

Doug, I've been learning Qlik for the last few months and I'm still struggling but amazed by the power of Qlik associative model, Scripting and Set analysis compared to SQL and other languages... but I'm still struggling:

I put together the following data:

Untitled.png

The first thing I would've done is this:

=Sum(IF(AGGR(sum(Orders),Customer,Region)>=100,Revenue))

Which got me this:

Untitled.png

Which is not correct because is not adding up all the occurrences that I was expecting

This could have been done by using just using Limitations on the the dimensions based on Sum(Orders) -or Quantity in your design-, but with the inconvenience that it's required to display that measure... so it's sort of like cheating...

Untitled.png

So, after continuing to struggle for a while I would've gone back to the first document I read on set analysis (attached) to confirm that I needed to find all the members that met the condition which is something like this:

=Sum({<Region&Customer = {"=Sum(Orders)>=100"}>}Revenue)    which is not the correct syntax so I needed a new single field that would give me each individual combination of Region and Customer (members)

load *,

     Region&'-'&Customer as RegionCustomer;

load * inline [

Region,Customer,Orders,Revenue

A,1,20,89

A,2,30,90

A,3,40,91...

Untitled.png

And of course, instead of wasting performance with that concatenation, the best option is what Sunny proposed!

load *,

     AutoNumber(Region&'-'&Customer) as RegionCustomerKey;

load * inline [

Region,Customer,Orders,Revenue

A,1,20,89

A,2,30,90

A,3,40,91

Untitled.png

I hope this helps,

Cheers,

Luis

Anonymous
Not applicable
Author

Thank you both Luis and Sunny!  I really appreciate the time you have taken to give me assistance.  For my first question, the effort and feedback provided is amazing.