topic Re: Sum(Field) in set analysis in QlikView

Sum(Field) in set analysis

Anonymous — Mon, 20 Jul 2015 08:43:45 GMT

Hi,

I work with Qlikview for 4 months now, and I need help using set analysis.

I have a simple table with 2 dimensions customer_id (=[Code Client]) and product_id (=[Code Produit]).

And I calculate the total spend (=[CA Net TTC Client]) in 2 periods of time defined by variables.

I would like it to calcule the sum of expression [Total Difference of spend] per customer only if the expression [Total Difference of spend] is >0.

I have created an expression which is called [Result I have], but it is different from what I would like (=[Result I would like to have]).

Here is the definition of my expression, can you help me please ?

if(

(

Sum({<[Jour de vente]={'>=$(=date($(v_p2_start))) <=$(=date($(v_p2_end)))'}>} [CA Net TTC Client])

Sum({<[Jour de vente]={'>=$(=date($(v_p1_start))) <=$(=date($(v_p1_end)))'}>} [CA Net TTC Client])

)>0,

(

Sum({<[Jour de vente]={'>=$(=date($(v_p2_start))) <=$(=date($(v_p2_end)))'}>} TOTAL <[Code Client]> [CA Net TTC Client])

Sum({<[Jour de vente]={'>=$(=date($(v_p1_start))) <=$(=date($(v_p1_end)))'}>} TOTAL <[Code Client]> [CA Net TTC Client])

)

Thanks in advance,
Sophie

Re: Sum(Field) in set analysis

jonas_rezende — Mon, 20 Jul 2015 20:29:22 GMT

Hi, Sophie Kamoun.

The result of the Total Difference of spend expression -120,32€ is less than 0. Please, help-me the understand.

With what criteria want to create [Result I would like to have]? Put example.

Hope this help!

Re: Sum(Field) in set analysis

Anonymous — Mon, 20 Jul 2015 20:56:31 GMT

Hi,

Is it something like this?

Re: Sum(Field) in set analysis

jonas_rezende — Mon, 20 Jul 2015 21:07:47 GMT

Hi.

Just you can say if your was problem resolved.

Thereforem, be aware that fabs () function takes the unsigned value, as QlikView Reference Manual.

"

fabs(x)

The absolute value of x. The result is a positive number.

Examples:

fabs( 2.4 ) returns 2.4

fabs( -3.8 ) returns 3.8

"

Regards,

Jonas Melo.

Re: Sum(Field) in set analysis

Anonymous — Fri, 24 Jul 2015 07:39:50 GMT

Hi Jonas Melo,

I want to sum the value of my column [Difference of spend] but only when values are >0.

In my example the result would be 122.66 = 28.33+28.33+32.00+34.00.

I don't know how to write the expression to get to that result.

Re: Sum(Field) in set analysis

sgrice — Fri, 24 Jul 2015 07:53:14 GMT

rangemax(

Sum({<[Jour de vente]={'>=$(=date($(v_p2_start))) <=$(=date($(v_p2_end)))'}>} [CA Net TTC Client])

Sum({<[Jour de vente]={'>=$(=date($(v_p1_start))) <=$(=date($(v_p1_end)))'}>} [CA Net TTC Client])

)

is a better form than your if

Re: Sum(Field) in set analysis

Anonymous — Fri, 24 Jul 2015 08:06:18 GMT

Thanks, that's almost what I want to do, I would like to sum it and display that sum on each row.

Any idea how to do that please ?

Re: Sum(Field) in set analysis

sgrice — Fri, 24 Jul 2015 08:10:12 GMT

So what columns do you want on the output

Re: Sum(Field) in set analysis

Anonymous — Fri, 24 Jul 2015 08:13:49 GMT

The column called [Result I have]