Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Qlick cloud sense where max(date) = date

I have a table with detailed of each user and he state in a course. This table is made by many concatenate tables which have an export date, so is a historic table with all the record of the years of the same people, so after that i would like a agroupation by the responsable email taking just the rows of the last export. 

So i do this and this does not work.

AGG_Historico:
LOAD
min(Responsable) as Responsable,
Responsable_email,
Responsable_email as email_DELEGADO_TERRITORIAL,
Responsable_email as email_JEFE_ZONA,
Responsable_email as email_JEFE_APARCAMIENTO,
count(Responsable_email) as total_Usuarios,
sum(NO_INICIADO) as numero_NO_INICIADO,
sum(EN_PROCESO) as numero_EN_PROCESO,
sum(COMPLETADO) as numero_COMPLETADO,
Avg(COMPLETADO) as percentage_COMPLETADO
resident Historico
where max(Fecha_exportacion) = Fecha_exportacion
group by Responsable_email;

 

without the where is works perfectly.

 

 

6 Replies
Partner
Partner

Hi,

Your problem is that max(Fecha_exportacion) is calculated for each row. You must calculate this date in a variable and then use it.

When you say max(Fecha_exportacion) = Fecha_exportacion, what do you mean?

Dan.
Highlighted
Contributor
Contributor

I mean that i have the records of all year of all the users so i want just to take the records belongin the last exportation date which in spanish is Fecha_exporcion, so i would like to do the same where but max(Fecha_exportacion ) should be the maximum date in this table. 

Highlighted
Partner
Partner

Well, this max(Fecha_Exportacion) is distinct for all user or is equal?


Highlighted
Contributor
Contributor

It will be the same since it is the last exportation date,

Highlighted
Partner
Partner

OK.
Try this:

Max_Fecha:
Load Date(Max(Fecha_Exportacion)) as MaxFechaExportacion Resideent Historico;
Let vMaxFechaExportacion = Peek('MaxFechaExportacion');
Drop Table Max_Fecha;

And use this variable into the where statment. Be careful with the date format.

AGG_Historico:
LOAD
min(Responsable) as Responsable,
Responsable_email,
Responsable_email as email_DELEGADO_TERRITORIAL,
Responsable_email as email_JEFE_ZONA,
Responsable_email as email_JEFE_APARCAMIENTO,
count(Responsable_email) as total_Usuarios,
sum(NO_INICIADO) as numero_NO_INICIADO,
sum(EN_PROCESO) as numero_EN_PROCESO,
sum(COMPLETADO) as numero_COMPLETADO,
Avg(COMPLETADO) as percentage_COMPLETADO
resident Historico
where Fecha_exportacion = '$(vMaxFechaExportacion)'
group by Responsable_email;

Dan.
Highlighted
Creator III
Creator III

If where Fecha_exportacion = '$(vMaxFechaExportacion)' doesn't work try Fecha_exportacion = vMaxFechaExportacion.  I forget how exactly single quotes work but I know without them if you have 7/5/19 it will divide 7 by 5 then divide that by 19 because it will calculate the variable.