Requirement using date functions

[raju_salmon]

Hi Experts,

I have requirement where i need to show a multibox/list box something like below values.

Select from below list

Yesterday

Lastweek

This week including Lastweek

Last two weeks

This month

This month including last month

I can able to create these date, however i need to link them with my date field. And how can i show them in list box??

Can you please provide me some ideas.

Thanks.

[sunny_talwar]

May be create flags for all of these in the master calendar and then link the flags to a table which allows you to select the names

LINKTable:

LOAD * INLINE [

FieldName, YesterdayFlag, LastWeekFlag, ....

Yesterday, 1, 0

Lastweek, 0, 1

This week including Lastweek, 0, 0

Last two weeks, 0, 0

This month, 0, 0

This month including last month, 0, 0

and so on... where YesterdayFlag will link to MasterCalendar.

It will form a synthetic key. If you are concerned about it, you can use a new key using AutoNumber function at the end.

HTH

Best,

Sunny

[raju_salmon]

I will try Sunny, thanks for information...

[raju_salmon]

Can anyone please give me any other solution for this? Thanks.

[MarcoWedel]

what was the outcome of your test of Sunny's solution?

regards

Marco

[MarcoWedel]

Hi,

without being sure how your flags are defined, one solution might be:

tabCalendar:  
LOAD *,  
     Day(Date) as Day,  
     WeekDay(Date) as WeekDay,  
     Week(Date) as Week,  
     WeekName(Date) as WeekName,  
     Month(Date) as Month,  
     MonthName(Date) as MonthName,  
     Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter,  
     QuarterName(Date) as QuarterName,  
     Year(Date) as Year,  
     WeekYear(Date) as WeekYear,     
     -(Date=Today()-1) as Yesterday,
     -InWeek(Date,Today(),-1) as Lastweek,
     -(InWeek(Date,Today(),-1) or InWeek(Date,Today(),0)) as [This week including Lastweek],
     -(Date>Today()-14 and Date<=Today()) as [Last two weeks],
     -InMonth(Date,Today(),0) as [This month],
     -(InMonth(Date,Today(),-1) or InMonth(Date,Today(),0)) as [This month including last month];    
LOAD Date(MinDate+IterNo()-1) as Date  
While MinDate+IterNo()-1 <= Today();  
LOAD MakeDate(2015) as MinDate  
AutoGenerate 1;

tabTemp:
CrossTable (DateFlag,FlagValue,11)
LOAD * Resident tabCalendar;

tabDateFlags:
LOAD Date, DateFlag
Resident tabTemp
Where FlagValue;

DROP Table tabTemp;

hope this helps

regards

Marco

[raju_salmon]

Hi Macro,

Thanks a lot for your time on this. This is excellent, working for me.

Can we control these dates based on input value?

For example, i will provide a box where user first enter a number then select any of above fields.

If he enter 3, and select "Last days completed" field, then it has to select last 3 completed days.

Thanks again for your time.

[raju_salmon]

Hi All,

Based on Macro reply, i can able to create almost similar flags which is required for my dashboard, however i need to control them with variable input values, any suggestions on this?

For example, I will provide below two flags to user with input box.

Last days including today,

Last weeks including this week

Now if user enter 3 in input box, and select 'Last days including today', last 3 days including today should be selected. or if he selects 'Last weeks including this week' then last 3 weeks including this week should be selected.

Thanks for your help.

Regards,

Raju

[raju_salmon]

Any suggestion macro?