Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
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
Highlighted
Specialist
Specialist

Re: Previous month : set analysis & variable

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

Re: Previous month : set analysis & variable

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

Re: Previous month : set analysis & variable

Try like this

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

Highlighted
Creator
Creator

Re: Previous month  : set analysis & variable

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

Re: Previous month : set analysis & variable

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