Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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!
Hi,
Is it something like this?
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.
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.
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
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 ?
So what columns do you want on the output
The column called [Result I have]