Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)
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))
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".
Not sure I understand... do you have a sample you can share?
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.
The expression above isn't working?
Value of eligible products in eligible orders should be 101 in attached app (made one in the end ).
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];
I attached another example, with more data.
I get some errors, like:
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)