Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

doug_kamen
New Contributor

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
Valued Contributor

Re: Set Analysis on aggr totals

Hi,

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

Please let me know,

Cheers,

Luis

6 Replies
luismadriz
Valued Contributor

Re: Set Analysis on aggr totals

Hi,

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

Please let me know,

Cheers,

Luis

doug_kamen
New Contributor

Re: Set Analysis on aggr totals

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
Contributor III

Re: Set Analysis on aggr totals

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

MVP
MVP

Re: Set Analysis on aggr totals

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
Valued Contributor

Re: Set Analysis on aggr totals

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

Highlighted
doug_kamen
New Contributor

Re: Set Analysis on aggr totals

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.

Community Browser