topic Re: Problem with expressions in QlikView

Problem with expressions

Thu, 17 Dec 2015 11:42:16 GMT

Hi everyone ,

I post this as i'm encountering kind of a problem with an expression In qlikview.

I ve been searching in the other posts but havn't seen an answer to my problem at the moment.

So let me show you: I have this table in Excel that i'd like to get in Qlikview to do a waterfall

Actually, i know how to do a waterfall but i need some help translating formulas for "crecen" which is " Increasing", for "decrecen" that is " decreasing"," nuevos" for "new one" and "perdidos" for " those that were lost"

In my script, i have this as fixed:

VARIABLES

vPeriodo

$(=Max(PeriodoID)

vMesAnterior

PeriodoID={"$(=Max(PeriodoID)-12)"}

In excel, i have for:

- "crecen" =IF(E2>0;E2;"") that i translated with "IF(Sum({<vPeriodo>}IMPORTE)-sum({$<$(vMesAnterior)>}IMPORTE) >>0,Sum({<vPeriodo>}IMPORTE)-sum({$<$(vMesAnterior)>}IMPORTE) )"

- "decrecen" =IF(E7<0;E7;"") that i translated with "IF(Sum({<vPeriodo>}IMPORTE)-sum({$<$(vMesAnterior)>}IMPORTE) <<0,Sum({<vPeriodo>}IMPORTE)-sum({$<$(vMesAnterior)>}IMPORTE) )"

- "perdidos" =IF(C2=0;E2;"") that i translated with "IF(Alt(Sum({<vPeriodo>}IMPORTE), 0),Sum({<vPeriodo>}IMPORTE)-sum({$<$(vMesAnterior)>}IMPORTE))"

- " nuevos" =IF(D16=0;SI(E16>0;E16;"");"") that i translated with "if(Alt(Sum({$<$(vMesAnterior)>}IMPORTE), 0),IF(Sum({<vPeriodo>}IMPORTE)-sum({$<$(vMesAnterior)>}IMPORTE) >>0,Sum({<vPeriodo>}IMPORTE)-sum({$<$(vMesAnterior)>}IMPORTE) ))"

Here is a copy of what i get in qlikview using these formulas

See that i get the same number for both Crecen/Decrecen/Perdidos/Nuevos which is, to my mind, not that easy to understand

Have i made a mystake?

Thank you for your attention,

Best regards,

Arno Cacciaguerra

Re: Problem with expressions

swuehl — Thu, 17 Dec 2015 14:00:22 GMT

At first glance there are several issues:

1) >> and << are bit shift operator, not comparison operator. Use > resp. < instead.

2) {<vPeriodo>} seems not to evaluate to a valid set expression.

It think you would need to use

{<$(vPeriodo)>}, with a variable definition like

PeriodoID={"$(=Max(PeriodoID))"}

Re: Problem with expressions

Fri, 18 Dec 2015 09:07:21 GMT

Tried to change >> and << first but this has no effect

Then i added this definition of the variable you gave me but here it went kinda wrong( changed it, in the expression, too)

Here is what happened

Re: Problem with expressions

Fri, 18 Dec 2015 09:27:14 GMT

To be honest i think that the problem comes from the way i ve been translating my formula from Excel to Qlikview as the data seems to be working well while using the first variables i gave.

The only thing that goes wrong is, to me, while calculating the expressions of "crecen,decrecen,nuevos,perdidos"

Re: Problem with expressions

swuehl — Fri, 18 Dec 2015 10:57:01 GMT

Could you upload your QVW, with some sample data (could be mock up data)?

Re: Problem with expressions

Fri, 18 Dec 2015 11:37:01 GMT

Here is what i m working with

Thanks for your help

Arno Cacciaguerra

Re: Problem with expressions

swuehl — Sat, 19 Dec 2015 01:17:13 GMT

There was still a bit shift operator at one place and the variable name was not correctly defined.

I also added an virtual table using aggr() function to do the calculations per PARENT_ID.

This is how far I got, maybe this could be a starting point for you to apply your requirements.