topic Re: Sum based on Sum({Set Analysis}) in QlikView

Sum based on Sum({Set Analysis})

avantime — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Sum based on Sum({Set Analysis})

sunny_talwar — Fri, 16 Jun 2017 12:32:09 GMT

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))

Re: Sum based on Sum({Set Analysis})

avantime — Fri, 16 Jun 2017 12:42:07 GMT

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".

Re: Sum based on Sum({Set Analysis})

sunny_talwar — Fri, 16 Jun 2017 12:43:44 GMT

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

Re: Sum based on Sum({Set Analysis})

avantime — Fri, 16 Jun 2017 12:52:52 GMT

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.

Re: Sum based on Sum({Set Analysis})

sunny_talwar — Fri, 16 Jun 2017 14:04:35 GMT

The expression above isn't working?

Re: Sum based on Sum({Set Analysis})

avantime — Fri, 16 Jun 2017 14:34:49 GMT

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

Re: Sum based on Sum({Set Analysis})

swuehl — Fri, 16 Jun 2017 14:52:23 GMT

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];

Re: Sum based on Sum({Set Analysis})

avantime — Mon, 19 Jun 2017 11:09:42 GMT

I attached another example, with more data.

I get some errors, like:

Re: Sum based on Sum({Set Analysis})

swuehl — Mon, 19 Jun 2017 21:57:19 GMT

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)

Re: Sum based on Sum({Set Analysis})

avantime — Tue, 20 Jun 2017 06:44:22 GMT

Thank you very much Stefan, this is just what I needed!

Re: Sum based on Sum({Set Analysis})

avantime — Tue, 20 Jun 2017 07:25:05 GMT

P.S. Had to add:

=Sum(

{$<

OrderID = {"=Sum({<ArticleGroup={14923},

InvoiceDate={'>=$(=num(makedate(2017,6,6)))<=$(=num(makedate(2017,6,30)))'},

ClientProfile={'Ldmtrhg Gtmrzhs'}>} Revenue) >=99 "},

ArticleGroup={14923},

InvoiceDate={'>=$(=num(makedate(2017,6,6)))<=$(=num(makedate(2017,6,30)))'},

ClientProfile={'Ldmtrhg Gtmrzhs'}

>} Revenue)

As the result showed the total value of the order instead of the total value for valid products.

Thanks again!