Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Alternative display of Values with a condition

Hi,

I want to calculate the average of Schnitt_SMS and Schnitt_WEB. Schnitt_SMS gives an mathematical error,

so that Schnitt WEB und SMS can not be calculated. Does anyone have a solution to solve this problem with the "Condition"-

Field.

....is not one of the two predictable....show only the predictable...

Thanks...QlikView x64 - [Hp14890_dataTest_Formel_BedingungenTest.gif

3 Replies
jerem1234
Valued Contributor II

Re: Alternative display of Values with a condition

Hi Ingo,

Try using the rangesum function, that way if there is a null, you will still get the other numbers since sum will treat it as 0. I also cleaned up your expressions to use set analysis. Used this expression:

rangesum(sum({<Kanal = {'WEB'}, Antwort = {'>=1'}>}Antwort)

/

count({<Kanal = {'WEB'}, Antwort = {'>=1'}>}Antwort)

,

sum({<Kanal = {'SMS'}, Antwort = {'>=1'}>}Antwort)

/

count({<Kanal = {'SMS'}, Antwort = {'>=1'}>}Antwort))

/

2

Please find attached.

Hope this helps!

Not applicable

Re: Alternative display of Values with a condition

Hey...

thanks...

If I use this expression I get this:

2.gif

the result should be 4.66 (Field Schnitt WEB und SMS). So, I need a condition that says...if one of the Values (in this example "Schnitt SMS" is not predictable, display only the other one (In this Example "Schnitt WEB") in the Field "Schnitt WEB und SMS"...

jerem1234
Valued Contributor II

Re: Re: Alternative display of Values with a condition

Im guessing if they are both predictable, then add them together and divide by 2 like you had in your expression?

Try using the alt function like:

alt((sum({<Kanal = {'WEB'}, Antwort = {'>=1'}>}Antwort)/count({<Kanal = {'WEB'}, Antwort = {'>=1'}>}Antwort)+

sum({<Kanal = {'SMS'}, Antwort = {'>=1'}>}Antwort)/count({<Kanal = {'SMS'}, Antwort = {'>=1'}>}Antwort))/2

,

sum({<Kanal = {'WEB'}, Antwort = {'>=1'}>}Antwort)

/

count({<Kanal = {'WEB'}, Antwort = {'>=1'}>}Antwort)

,

sum({<Kanal = {'SMS'}, Antwort = {'>=1'}>}Antwort)

/

count({<Kanal = {'SMS'}, Antwort = {'>=1'}>}Antwort))

PFA

Hope this helps!

Community Browser