NumSum and If statement in Expression

[janturon]

Hello,

can you help me with this problem?

I have this definition in expression:

=NumSum((100-(Sum(U504)/Sum(U604))*100)/100)

and a I need to combine it with if statement like this:

=Sum( if( OBDOBIE >= $(vStartDateCompare) AND OBDOBIE <= $(vEndDateCompare), NumSum((100-(Sum(U504)/Sum(U604))*100)/100)) )

but it doesnt work.

thx.

[marcus_sommer]

Try something like this:

if(OBDOBIE >= $(vStartDateCompare) AND OBDOBIE <= $(vEndDateCompare),

     (100-(Sum(U504)/Sum(U604))*100)/100))

If you have only one argument you didn't need really numsum() or newer rangesum() unless it's only an argument inside another expression.

- Marcus

[alexandros17]

I think that the problem is different date format between OBDOBIE and $(vStartDateCompare)

Try to replace  $(vStartDateCompare)  with a fixed date (e.g. 14/01/2015) and verify if it works

let me know

[janturon]

no result 😞

[janturon]

I tried it, but no result... only "-"

This statement is function:

=Sum( if( OBDOBIE >= $(vStartDateCompare) AND OBDOBIE <= $(vEndDateCompare), U504) )

but problem is, when I want to replace U504 with an aggregation function.

[marcus_sommer]

Replace for testing the variablen with a fixed value and put the variablen in a textbox to see what will be returned. Further make sure that no part of your expression could return NULL or the divisor returned 0. Maybe so:

alt(sum(U504), 0) / alt(sum(604), 1)

- Marcus

[janturon · Accepted solution]

Solved ! 🙂

Like this:

100-((Sum( {<OBDOBIE={">=$(vStartDateCompare)<=$(vEndDateCompare)"}>} U504) / Sum( {<OBDOBIE={">=$(vStartDateCompare)<=$(vEndDateCompare)"}>} U604))*100)