Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
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,

Tags (1)
1 Solution

Accepted Solutions
AxNoxy
New Contributor III

Re: Sum of an aggregation if the aggregation meets a calculated threshold

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

Regards!

View solution in original post

9 Replies
vishsaggi
Esteemed Contributor III

Re: Sum of an aggregation if the aggregation meets a calculated threshold

Can you share some sample data with an expected output.

Not applicable

Re: Sum of an aggregation if the aggregation meets a calculated threshold

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

Re: Sum of an aggregation if the aggregation meets a calculated threshold

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

Re: Sum of an aggregation if the aggregation meets a calculated threshold

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

Re: Sum of an aggregation if the aggregation meets a calculated threshold

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

Regards!

View solution in original post

Not applicable

Re: Sum of an aggregation if the aggregation meets a calculated threshold

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

suryaa30
Contributor II

Re: Sum of an aggregation if the aggregation meets a calculated threshold

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

Re: Sum of an aggregation if the aggregation meets a calculated threshold

May be this

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

suryaa30
Contributor II

Re: Sum of an aggregation if the aggregation meets a calculated threshold

Perfect thanks Much Sunny.