topic Re: Date (month-1) in QlikView

Date (month-1)

Wed, 05 Oct 2016 14:39:47 GMT

Hey Guys, how are you?

I have a chart which use the dimension date ('dd'.'mm').

I want to show in the same chart, the same day but of month-1.

Do you guys know how to handle with it?

Kind Regards, Bisco

Re: Date (month-1)

sunny_talwar — Wed, 05 Oct 2016 14:42:30 GMT

May be using set analysis:

Sum({<DateField = {"$(=Date(AddMonths(Max(Date), -1), 'DateFieldFormatHere'))"}>} Measure)

Re: Date (month-1)

Anonymous — Wed, 05 Oct 2016 14:44:39 GMT

try this

Date(AddMonths(Max(date), -1), 'DD-MM')

Re: Date (month-1)

Wed, 05 Oct 2016 15:02:20 GMT

Thank you, again Sunny!

I did, but it went wrong, look at this:

Sum({<Data_Carteira = {"$(=Date(AddMonths(Max(Date), -1), 'DD.MM'))"}>} Produzido), is the sintax wrong?

Re: Date (month-1)

sunny_talwar — Wed, 05 Oct 2016 15:03:53 GMT

May be this:

Sum({<Data_Carteira = {"$(=Date(AddMonths(Max(Data_Carteira), -1), 'DD.MM'))"}>} Produzido)

Re: Date (month-1)

Wed, 05 Oct 2016 15:11:17 GMT

Not work yet! 😕

Re: Date (month-1)

sunny_talwar — Wed, 05 Oct 2016 15:13:18 GMT

Do you have other date related fields where you might be making selections? Like Month, Year etc? You might have to ignore selection in those fields

Sum({<Data_Carteira = {"$(=Date(AddMonths(Max(Data_Carteira), -1), 'DD.MM'))"}, Year, Month, MonthYear, MonthNum, Quarter>} Produzido)

Re: Date (month-1)

Wed, 05 Oct 2016 16:11:10 GMT

I'm not getting it, I've been thinking about duplicating the data on script, but it will make it too big...

Re: Date (month-1)

sunny_talwar — Wed, 05 Oct 2016 16:40:34 GMT

You can use this as this won't take a lot of space:

The As-Of Table

Re: Date (month-1)

MarcoWedel — Wed, 05 Oct 2016 22:28:47 GMT

Hi,

maybe one solution might be:

Sum({$<Mês_Carteira={">=$(=AddMonths(Max(Mês_Carteira),-1))<=$(=Max(Mês_Carteira))"}>} Produzido)

table1:
LOAD Date(YearStart(Today())+RecNo()-1) as Data_Carteira,
     Ceil(Rand()*100+100) as Produzido
AutoGenerate DayNumberOfYear(Today());

tabCalendar:
LOAD *,
     Day(Data_Carteira) as Dia_Carteira,
     WeekDay(Data_Carteira) as WeekDay,
     Week(Data_Carteira) as Week,
     WeekName(Data_Carteira) as WeekName,
     Month(Data_Carteira) as Month,
     MonthName(Data_Carteira) as Mês_Carteira,
     Dual('Q'&Ceil(Month(Data_Carteira)/3),Ceil(Month(Data_Carteira)/3)) as Quarter,
     QuarterName(Data_Carteira) as QuarterName,
     Year(Data_Carteira) as Year,
     WeekYear(Data_Carteira) as WeekYear;  
LOAD Date(MinDate+IterNo()-1) as Data_Carteira
While MinDate+IterNo()-1 <= MaxDate;
LOAD Min(Data_Carteira) as MinDate,
     Max(Data_Carteira) as MaxDate
Resident table1;

hope this helps

regards

Marco