cancel
Showing results for
Did you mean:
Not applicable

## Evolution pourcentage over years

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

JP

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

14 Replies

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

or

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

- Marcus

MVP

The definitions of your variables should be

=Max(YEAR)-1

resp.

=Max(YEAR)

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

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

Not applicable
Author

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_*')
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
;

Not applicable
Author

Sorry i am new

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

MVP

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.

Partner - Creator III

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

Not applicable
Author

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

Partner - Creator III

/Teis

Not applicable
Author

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.)])
)

Community Browser