Date (month-1)

[#1]

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

[sunny_talwar]

May be using set analysis:

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

[Anonymous]

hi

try this

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

[#4]

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?

[sunny_talwar]

May be this:

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

[#6]

Not work yet! 😕

[sunny_talwar]

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)

[#8]

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

[sunny_talwar]

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

The As-Of Table

[MarcoWedel · Accepted solution]

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