Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Previous month : set analysis & variable

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 !

5 Replies
Specialist
Specialist

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)

Highlighted
Creator
Creator

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.

Highlighted
Master III
Master III

Try like this

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

Highlighted
Creator
Creator

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,

Highlighted
Specialist
Specialist

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