topic Re: Evolution pourcentage over years in QlikView

Evolution pourcentage over years

Mon, 21 Sep 2015 09:57:56 GMT

Hello there,

i am currently working with a file containing information per year

i have the list of region and number of roomnights. below this is the request of my business user.

Maybe it is because it is monday, but i can not figure out how to solve it ... could you please help ?

I was thinking about using variable like this

and the formula

fabs(
(
//Année MAX
SUM({$<YEAR={$(vYear)}>} [Nuitées (p.p.)])

// Année Max - 1
-
SUM({$<YEAR={$(vPrevYear)}>} [Nuitées (p.p.)])
// Année Max - 1 (Référentiel)
)
/
SUM({$<YEAR={$(vPrevYear)}>} [Nuitées (p.p.)])
)

But it is not working at all....thanks for your help

Re: Evolution pourcentage over years

marcus_sommer — Mon, 21 Sep 2015 10:03:18 GMT

Have you LET within the variable-value? You need these LET or SET only within the script - within the gui a variable looked so:

= max(Year) // if the variable should evaluate this

max(Year) // if it should be a string for uses in other expressions like: $(variable)

- Marcus

Re: Evolution pourcentage over years

swuehl — Mon, 21 Sep 2015 10:03:55 GMT

The definitions of your variables should be

=Max(YEAR)-1

resp.

=Max(YEAR)

without a leading LET VariableName.

Have you defined the variables in the script? Then please post your script code.

If not, remove the LET Variablename in Variable overview editor.

Re: Evolution pourcentage over years

Mon, 21 Sep 2015 10:05:51 GMT

this is my current script

SET ThousandSep="'";
SET DecimalSep='.';
SET MoneyThousandSep="'";
SET MoneyDecimalSep='.';
SET MoneyFormat='fr. #''##0.00;fr.-#''##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';
SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';

for Each File in FileList('M:\Userdat\_Marketing Loisirs\PROJETS_PRODUITS\JPF\Ongoing\Projects P-T\Qlickview\OFstat\Chiffres pour Qlik_Pays de Provenance_*')
LOAD F1,
Année,
Mois,
[Région touristique STA],
[Catégorie d'étoiles],
[Pays d'origine nom],
//if(match([Pays d'origine nom],'Hong Kong','HongKong'), 'Regroup',[Pays d'origine nom]) as Hong-Kong,
[Pays d'origine numero],
[Arrivées (p.p.)],
[Nuitées (p.p.)],
[Durée de séjour]
FROM
$(File)
(biff, embedded labels);
;

NEXT File
;

Re: Evolution pourcentage over years

Mon, 21 Sep 2015 10:14:23 GMT

Sorry i am new

does that mean that if you are using the VARIABLES, you are NOT using the LET in the Scripting ? right ?

Re: Evolution pourcentage over years

swuehl — Mon, 21 Sep 2015 10:22:08 GMT

LET and SET are script statements, used for to define variables in the script.

If you create the variables in the front end, using variable overview, you don't use LET and SET statements.

So, open the variable overview, click on vYear and copy into the editor:

=max(YEAR)

Similar for the vPrevYear variable.

If you then create an input box with your variables, you should see the actual values (like 2015) in the input box.

Re: Evolution pourcentage over years

teiswamsler — Mon, 21 Sep 2015 10:24:47 GMT

Hi Jean-Philippe

From where do you get the field Year?

SUM({$< YEAR ={$(vYear)}>} [Nuitées (p.p.)])

t it possible to get qvw file, to help?

/Teis

Re: Evolution pourcentage over years

Mon, 21 Sep 2015 11:09:31 GMT

you are right ....I should not use YEAR but Année as in my script.....poor me...it is monday

Re: Evolution pourcentage over years

teiswamsler — Mon, 21 Sep 2015 11:20:07 GMT

Np, i am glad to help you, help you...

/Teis

Re: Evolution pourcentage over years

Mon, 21 Sep 2015 11:26:44 GMT

I replaced year by Année and the formula using N-1 and N is still not fonctionning

fabs(
(
//Année MAX
SUM({$<Année={$(vYear)}>} [Nuitées (p.p.)])

// Année Max - 1
-
SUM({$<Année={$(vPrevYear)}>} [Nuitées (p.p.)])
// Année Max - 1 (Référentiel)
)
/
SUM({$<Année={$(vPrevYear)}>} [Nuitées (p.p.)])
)

Re: Evolution pourcentage over years

Anonymous — Mon, 21 Sep 2015 11:32:05 GMT

can you just share a sample qvw .

it will be easy to help you .

Thanks

BKC

Re: Evolution pourcentage over years

teiswamsler — Mon, 21 Sep 2015 11:32:54 GMT

Hi Jean-Philippe

try making a text box, set in ='SUM({$<Année={$(vYear)}>} [Nuitées (p.p.)])' this will show you the calutation of the Variable in the set analysis..

/Teis

Re: Evolution pourcentage over years

Anonymous — Mon, 21 Sep 2015 11:35:41 GMT

for previous Year

Sum({$<Année = {$(=Max(Année)-1)}>}[Nuitées (p.p.)])

for current year or max year

Sum({$<Année = {$(=Max(Année))}>}[Nuitées (p.p.)])

Thanks

BKC

Re: Evolution pourcentage over years

Mon, 21 Sep 2015 12:28:23 GMT

so it is not necessary to add some variables right ??

Re: Evolution pourcentage over years

Anonymous — Mon, 21 Sep 2015 12:30:58 GMT

yes you dont need variables

if you want you can add .. its up to you

Thanks

BKC