Sum based on Sum({Set Analysis})

[avantime]

Hi,

I want to calculate the Sum of Revenue where

Sum(

{$<

[Product group]={1},

[Invoice date]={"$(='>=' & num(date('01/06/2017','DD/MM/YYYY')) & '<=' & num(date('30/06/2017','DD/MM/YYYY')))"},

[Client profile]={'Valued'}

>}  Revenue)>=100

Any ideas?

[sunny_talwar]

May be this

If(Sum({$<

[Product group]={1},

[Invoice date]={"$(='>=' & num(date('01/06/2017','DD/MM/YYYY')) & '<=' & num(date('30/06/2017','DD/MM/YYYY')))"},

[Client profile]={'Valued'}>}  Revenue)>=100,

Sum({$<

[Product group]={1},

[Invoice date]={"$(='>=' & num(date('01/06/2017','DD/MM/YYYY')) & '<=' & num(date('30/06/2017','DD/MM/YYYY')))"},

[Client profile]={'Valued'}>}  Revenue))

[avantime]

Hi Sunny,

Forgot to add something

I want to calculate the Sum of Revenue where

Sum(

{$<

[Product group]={1},

[Invoice date]={"$(='>=' & num(date('01/06/2017','DD/MM/YYYY')) & '<=' & num(date('30/06/2017','DD/MM/YYYY')))"},

[Client profile]={'Valued'}

>}  TOTAL <ClientID,OrderID> Revenue)>=100

So valid products are the ones with totals of over 100 per OrderID. 

If I use

IF(

Sum(

{$<

[Product group]={1},

[Invoice date]={"$(='>=' & num(date('01/06/2017','DD/MM/YYYY')) & '<=' & num(date('30/06/2017','DD/MM/YYYY')))"},

[Client profile]={'Valued'}

>}  TOTAL <ClientID,OrderID> Revenue)>=100

,

Sum(

{$<

[Product group]={1},

[Invoice date]={"$(='>=' & num(date('01/06/2017','DD/MM/YYYY')) & '<=' & num(date('30/06/2017','DD/MM/YYYY')))"},

[Client profile]={'Valued'}

>}  TOTAL <ClientID,OrderID> Revenue))

Shows the total for all the products disregarding the rule that "valid products are the ones with totals of over 100 per OrderID". 

[sunny_talwar]

Not sure I understand... do you have a sample you can share?

[avantime]

Very sorry, don`t have a sample to share.

Let me explain in a different way.

Right now I am doing this count:

count(distinct aggr(if(Sum(

{$<

[Product group]={1},

[Invoice date]={"$(='>=' & num(date('01/06/2017','DD/MM/YYYY')) & '<=' & num(date('30/06/2017','DD/MM/YYYY')))"},

[Client profile]={'Valued'}

>}  TOTAL <ClientID,OrderID> Revenue)>=100

,OrderID),OrderID))

So it shows me how many orders have products from product group 1 that total more than 100 per order, being sold between 01.06.2017 and 30.06.2017 to Valued clients.

I want to calculate the sum of these products.

[sunny_talwar]

The expression above isn't working?

[avantime]

Value of eligible products in eligible orders should be 101 in attached app (made one in the end ).

[swuehl]

You get 101 in your expression after correcting your script:

Set DateFormat = 'DD/MM/YYYY';

LOAD * INLINE

[Invoice Date,OrderID,ClientID,Product ID,Revenue

01/06/2016,54,1,A,4

01/06/2016,54,1,B,97

01/06/2016,54,1,C,21

01/06/2016,54,1,D,32

31/05/2016,32,2,A,109

21/06/2016,122,3,A,74

21/06/2016,125,3,C,250

21/06/2016,135,3,D,74];

LOAD * INLINE

[ClientID,Client profile

1,Profile1

2,Profile2

3,Profile3];

LOAD * INLINE

[Product ID,Product group

A,1

B,1

C,2

D,3];

[avantime]

I attached another example, with more data.

I get some errors, like:

[swuehl · Accepted solution]

Maybe using an advanced search to filter your order IDs:

=Sum(

{$<

OrderID = {"=Sum({<ArticleGroup={14923},InvoiceDate={'>=$(=num(makedate(2017,6,6)))<=$(=num(makedate(2017,6,30)))'},ClientProfile={'Ldmtrhg Gtmrzhs'}´>} Revenue) >=99 "}

>} Revenue)