Skip to main content
cancel
Showing results for 
Search instead 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

2015-09-21_11h56_19.png

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

2015-09-21_11h53_50.png

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

View solution in original post

14 Replies
marcus_sommer

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

swuehl
MVP
MVP

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.

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

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 ?

swuehl
MVP
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.

teiswamsler
Partner - Creator III
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

teiswamsler
Partner - Creator III
Partner - Creator III

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

/Teis

Not applicable
Author

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

2015-09-21_13h25_46.png

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