Set Analysis on aggr totals

[Anonymous]

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?

[luismadriz · Accepted solution]

Hi,

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

Please let me know,

Cheers,

Luis

[Anonymous]

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]

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]

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:

The first thing I would've done is this:

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

Which got me this:

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

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

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

I hope this helps,

Cheers,

Luis

[Anonymous]

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.