topic Re: NumSum and If statement in Expression in Qlik Learning Discussions

NumSum and If statement in Expression

janturon — Wed, 07 Jan 2015 13:00:32 GMT

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.

Re: NumSum and If statement in Expression

marcus_sommer — Wed, 07 Jan 2015 13:48:10 GMT

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

Re: NumSum and If statement in Expression

alexandros17 — Wed, 07 Jan 2015 13:58:50 GMT

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

Re: NumSum and If statement in Expression

janturon — Thu, 08 Jan 2015 05:23:48 GMT

no result 😞

Re: NumSum and If statement in Expression

janturon — Thu, 08 Jan 2015 05:26:07 GMT

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.

Re: NumSum and If statement in Expression

marcus_sommer — Thu, 08 Jan 2015 08:15:58 GMT

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

Re: NumSum and If statement in Expression

janturon — Mon, 26 Jan 2015 09:42:41 GMT

Solved ! 🙂

Like this:

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