Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sum of an aggregation if the aggregation meets a calculated threshold

Hi Community,

I am trying to get the sum sales of all customers that have total sales above a certain level. The issue I'm running into is that the data is at a transactional level, so the formula must first aggregate sales at the customer level and then assess those aggregated values.


I have tried the following without luck:

IF(

     AGGR(SUM(Sales), Customer) >= v.CUSTFRACTILE,

     SUM(SALES))

and

IF(

     AGGR(SUM(Sales), Customer) >= v.CUSTFRACTILE,

     SUM(AGGR(SUM(Sales), Customer)))

Any help would be greatly appreciated!

Thank you,

1 Solution

Accepted Solutions
AxNoxy
Contributor III
Contributor III

Something like this ?2017-09-07_18h29_23.png

Regards!

View solution in original post

9 Replies
vishsaggi
Champion III
Champion III

Can you share some sample data with an expected output.

Not applicable
Author

Hi Vishwarath,

I can't share the actual data but I have created a simple example below:

Example for QV.PNG

If in this example I'm looking for the sum of sales for all customers that have sales of at least 400, the correct answer would be 1430 (sum of sales for customers B and C since they spent 430 and 1000 respectively).

vishsaggi
Champion III
Champion III

Are you looking for this?

Capture.PNG

Assuming that you already have set the variable vCustfractile to 400.

Then in your straight table add Customer and Transaction and expression as

= IF(Sum(Aggr(Sum(SalesAmt), Customer)) >= vCustfractile, Sum(Aggr(Sum(SalesAmt), Customer)))

With Total Mode as Sum of Rows Radio check.

Not applicable
Author

Thanks, Vishwarath.

I'm looking to get the number to display in a text  box since I'm using it as a KPI at the top of a dashboard. Is there a way of calculating this without using a straight table (or alternately referencing a straight table total from a text box)?

AxNoxy
Contributor III
Contributor III

Something like this ?2017-09-07_18h29_23.png

Regards!

View solution in original post

Not applicable
Author

The SUM(IF( statement works perfectly - Thank you!

suryaa30
Creator II
Creator II

How to get this 1430 in a text box using set expression?  assuming we have vCUSTFRACTILE as variable.

sunny_talwar

May be this

=Sum({<Cutomer = {"=Sum(Sales) > $(vCUSTFRACTILE)"}>}Sales)

suryaa30
Creator II
Creator II

Perfect thanks Much Sunny.