Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Date (month-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

1 Solution

Accepted Solutions
MarcoWedel

Hi,

maybe one solution might be:

QlikCommunity_Thread_235354_Pic1.JPG

QlikCommunity_Thread_235354_Pic3.JPG

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

QlikCommunity_Thread_235354_Pic2.JPG

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

View solution in original post

9 Replies
sunny_talwar

May be using set analysis:

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

galax_allu
Specialist
Specialist

hi

try this

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



Not applicable
Author

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)

Not applicable
Author

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)

Not applicable
Author

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

Hi,

maybe one solution might be:

QlikCommunity_Thread_235354_Pic1.JPG

QlikCommunity_Thread_235354_Pic3.JPG

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

QlikCommunity_Thread_235354_Pic2.JPG

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

View solution in original post