recupération des valeurs dernieres colonnes d'un tableau

[Hamdi_G]

svp avec un tableau à plusieurs colonnes et remplies de données cette formule ne retourne aucun résultat  alors qu'elle doit me donner la première valeur avec la plus grande date:

FirstSortedValue(Valeur,-Date,1)

quel pourrait être le problème ?

[Saravanan_Desingh]

If the Max(Date ) has more than one 'Value', then this will result NULL. Please check the Table.

[Hamdi_G]

There is only one 'Valeur' for the max(Date)

[Saravanan_Desingh]

Please share the sample data here. Let me look into it.

[Taoufiq_Zarra · Accepted solution]

La version longue selon ce que tu as communiqué ici :

https://community.qlik.com/t5/New-to-Qlik-Sense/Moyenne-des-4-derni%C3%A8re-colonnes-d-un-tableau/m-...

Data:

LOAD

VmDmd,
    DateDmd,
BatterieDmd,
UtilisationDmd,
[A.h Consommee]
 FROM .\Downloads\output.csv
(txt, utf8, embedded labels, delimiter is ',', msq);

Dataoutput:

load distinct BatterieDmd resident Data;

Final:

LOAD 1 as Tmp autogenerate 1;

For vRow = 1 to NoOfRows('Dataoutput')

Let MyVar = Peek('BatterieDmd',vRow-1,'Dataoutput');


Tmp:
noconcatenate

load * resident Data where BatterieDmd='$(MyVar)' order by DateDmd ;

Let vMin = Floor(Peek('DateDmd',0));

Let vMax = Floor(Peek('DateDmd',-1));


Join LOAD Date($(vMin))+recno()-1 as DateDmd

AutoGenerate $(vMax)-$(vMin)+1;


outputTmp:
noconcatenate

load * resident Tmp order by DateDmd DESC;

drop table Tmp;

    Join(Final)
load if(IsNull(UtilisationDmd),peek(UtilisationDmd),UtilisationDmd) as UtilisationDmd,if(IsNull(VmDmd),peek(VmDmd),VmDmd) as VmDmd,if(IsNull(BatterieDmd),peek(BatterieDmd),BatterieDmd) as BatterieDmd,DateDmd,Year(DateDmd) as Année,Week(DateDmd) as Semaine  ,if(IsNull([A.h Consommee]),0,[A.h Consommee]) as [A.h Consommee] resident outputTmp;


drop table outputTmp;

Next
drop table Data,Dataoutput;
DROP Field Tmp;




Tmp0:
noconcatenate

load [BatterieDmd],Semaine,avg(DateDmd) as DateDmdSort,Année, avg([A.h Consommee]) as MoyenParSemaine resident Final group by [BatterieDmd],Année,Semaine ;

drop table Final;


Data_:
noconcatenate

load * resident Tmp0 order by BatterieDmd,Année,Semaine;

drop table Tmp0;


Tmp_:
noconcatenate

load *, if(rowno()=0,0,if(previous([BatterieDmd])=[BatterieDmd],peek(Val)+1,0)) as Val

resident Data_;

drop table Data_;


output:
noconcatenate
load BatterieDmd,
RangeAvg(FirstSortedValue(MoyenParSemaine,-Val,1),FirstSortedValue(MoyenParSemaine,-Val,2),FirstSortedValue(MoyenParSemaine,-Val,3),FirstSortedValue(MoyenParSemaine,-Val,4)) as Moyenne
resident Tmp_   group by BatterieDmd;