Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

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

View solution in original post

6 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted

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

Highlighted
New Contributor III

Re: NumSum and If statement in Expression

no result 😞

Highlighted
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.

Highlighted
MVP & Luminary
MVP & Luminary

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

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

View solution in original post