Sum(Field) in set analysis

[Anonymous]

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

       ),

       0

)

Thanks in advance,
Sophie

[jonas_rezende]

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!

[Anonymous]

Hi,

Is it something like this?

[jonas_rezende]

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.

[Anonymous]

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.

[sgrice · Accepted solution]

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

,0

)

is a better form than your if

[Anonymous]

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 ?

[sgrice]

So what columns do you want on the output

[Anonymous]

The column called [Result I have]