Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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.
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
;
Sorry i am new
does that mean that if you are using the VARIABLES, you are NOT using the LET in the Scripting ? right ?
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.
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
you are right ....I should not use YEAR but Année as in my script.....poor me...it is monday
Np, i am glad to help you, help you...
/Teis
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.)])
)