Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

janturon
New Contributor III

NumSum and If statement in Expression

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.

Tags (2)
1 Solution

Accepted Solutions
janturon
New Contributor III

Re: NumSum and If statement in Expression

Solved ! :-)

Like this:

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

6 Replies

Re: NumSum and If statement in Expression

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

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
New Contributor III

Re: NumSum and If statement in Expression

no result :-(

janturon
New Contributor III

Re: NumSum and If statement in Expression

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

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
New Contributor III

Re: NumSum and If statement in Expression

Solved ! :-)

Like this:

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

Community Browser