topic Re: Previous month : set analysis & variable in QlikView

Previous month : set analysis & variable

marieclaire — Wed, 25 Nov 2020 16:16:04 GMT

Hi Everybody,

I calculated Previous month as :

num( if (max(Date.Mois)=1, 12, max(Date.Mois)-1),'00').

It works, but i can not manage to use it in this set analysis :

count( {<Date.Mois = {$(=num( if(max(Date.Mois)=1, 12, max(Date.Mois)-1) ,'00'))}>} DISTINCT id_content)

Also, i would like to use a variable as $(Var.Date.M-1)

I do not manage to use it :

count ({$ <Date.Mois = { '$( =$(Var.Date.M-1) )' }>} DISTINCT id_content)

Any idea ?

Thank you !

Re: Previous month : set analysis & variable

andrey_krylov — Sat, 29 Sep 2018 17:07:25 GMT

Hi Christiane, try this

count( {<Date.Mois = {"$(=num( if(max(Date.Mois)=1, 12, max(Date.Mois)-1) ,'00'))"}>} DISTINCT id_content)

and this

count ({$ <Date.Mois = { '$(Var.Date.M-1)' }>} DISTINCT id_content)

Re: Previous month : set analysis & variable

marieclaire — Sat, 29 Sep 2018 23:13:40 GMT

Thank you Andrey, but it does not work.

I suppose a have problems with the dates format, which does not match.

The only Date which gives good result when requesting is @CLE_Date.Publication.

But i do not manage to use the field @Date, which is supposed to match with all the other dates.

May be it comes from the Calendar :

======================================================

LET Var.Date.Min = Num(Makedate(2018,1,1));

LET Var.Date.Max = Num(Makedate(Year(Now()),Month(Now()),Day(Now())));

let Var.Nb.Days=Var.Date.Max-Var.Date.Min;

ChampDate:

LOAD date($(Var.Date.Min)+IterNo()-1) as @Date

AUTOGENERATE (1)

WHILE $(Var.Date.Min)+IterNo()-1<= $(Var.Date.Max);

CALENDRIER_EquipeObjectif:

LOAD @Date as @CLE_Date,

@Date as @CLE_Date.Publication,

month(@Date) as @CLE_CAL_OBJ_Date.Mois,

num(Month(@Date),00) as CAL_OBJ_MoisObjectif,

num(Year(@Date)) as CAL_OBJ_AnneeObjectif

Resident ChampDate;

JOIN

LOAD DISTINCT

@CLE_EQUIP_EquipeObjectif

Resident REF.equipes;

DROP TABLE ChampDate;

left join

LOAD

@CLE_EQUIP_EquipeObjectif as @CLE_EQUIP_EquipeObjectif,

@CLE_Editeur.Equipe.AnnéeMois,

num(REF.OBJ.objectif.mois,00) as CAL_OBJ.MoisObjectif,

num(REF.OBJ.objectif.annee) as CAL_OBJ.AnneeObjectif,

REF.OBJ.objectif.min as CAL_OBJ.objectif.min,

REF.OBJ.objectif.max as CAL_OBJ.objectif.max,

REF.OBJ.objectif.sourceprod as CAL_OBJ.objectif.sourceprod,

REF.OBJ.objectif.libelle as CAL_OBJ.objectif.libelle

Resident REF.objectifs;

CALENDRIER_tmp:

LOAD DISTINCT

Date(LEA_date_publication) as @CLE_Date

resident LEA_contenus;

LOAD

@CLE_Date

resident CALENDRIER_EquipeObjectif ;

CALENDRIER:

LOAD DISTINCT

Date(@CLE_Date) as @CLE_Date,

Year(@CLE_Date) as Date.Année,

Year(@CLE_Date)&'-'& num(Month(@CLE_Date),'00') as Date.AnnéeMois,

Week (@CLE_Date) as Date.Semaine,

WeekStart (@CLE_Date) as Date.Semaine.Début,

WeekEnd (@CLE_Date) as Date.Semaine.Fin,

MonthName(@CLE_Date) as Date.AnnéeMois.nom,

MonthName(@CLE_Date) as Date.Mois.nom,

QuarterName(@CLE_Date) as Date.Trim.nom,

Month(QuarterStart(@CLE_Date)) as Date.Trim.mois,

YearToDate(@CLE_Date) as Date.YTD

resident CALENDRIER_tmp;

let Var.Date.Min=null();

let Var.Date.Max=null();

let Var.Nb.Days=null();

drop Table CALENDRIER_tmp;

drop Table REF.objectifs;

drop Table REF.equipes;

Anyway, thank you.

Re: Previous month : set analysis & variable

sasiparupudi1 — Mon, 01 Oct 2018 10:16:06 GMT

Try like this

Date.Mos={"$(=Date(AddMonths(Max(Date.Mos),-1),'ÝOURFORMAT'))"}

Re: Previous month : set analysis & variable

marieclaire — Mon, 08 Oct 2018 14:35:16 GMT

Thank you but it does not work.

For now, the only expression i can use is :

num( if (max(Date.Mois)=1, 12, max(Date.Mois)-1),'00')

And i can not make it work with the variable $(Var.Date.M-1)

regards,

Re: Previous month : set analysis & variable

andrey_krylov — Mon, 08 Oct 2018 15:05:21 GMT

Hi Christiane. I looked at the file and did not find the field Date.Mois in it.