To make a logic dynamic

[santhana]

Hello Friends

I need to identify  Mobile numbers as Repeat if they were available upto end of previous quarter. 

Example: if mobile no in May is also available before 31st March (upto last Quarter)  then it is repeat. Else fresh.

      I am able to achieve the above , by using below apply map code. However its not dynamic for Year/Month selections (Due to use of today() function).

Is there any way around to get it for previous years as well. Eg:  if we select May 2018 , it should search upto Mar 31st ,2018

Code:

Mapp_Qtr:
Mapping
Load
Mobile_No,
'Repeat'
From
[C:\.......\QVD\Final\Final.qvd]
(qvd) where Date<=QuarterStart(today());

LOAD
Lead,
Status,
Quarter,
Key,
Month,
if( Date>=QuarterStart(today()) and Date<=QuarterEnd(today()), ApplyMap('Mapp_Qtr',Mobile_No,'Fresh')) as Lead_Type,

from

From
[C:\.......\QVD\Final\Final.qvd]
(qvd);

//Let me know if more information is required. Thanks in advance

Thanks & Regards

Santhana

[michaelsikora]

Hello Santhana,

Why don't you try loading in a larger amount of data? For example in your WHERE function, use this:

where Date>= addmonths(YearStart(Today(0)),-48);

Then you can have a date range tied to your date table similar to the following couple of examples.

LET vToday=today(1)
;
Ranges:
LOAD
Range,
date(evaluate(RangeStart)) as RangeStart,
date(evaluate(RangeEnd)) as RangeEnd
;
LOAD * INLINE [
Range; RangeStart; RangeEnd
Today; vToday; DayEnd(vToday)
Yesterday; vToday-1; DayEnd(vToday-1)
This Week; WeekStart(vToday,0,-1); WeekEnd(vToday,0,-1)
Last Week; WeekStart(vToday,-1,-1); WeekEnd(vToday,-1, -1)
This Month; MonthStart(vToday); MonthEnd(vToday)
Last Month; MonthStart(vToday,-1); MonthEnd(vToday,-1)
This Year; YearStart(vToday); YearEnd(vToday)
Last Year; YearStart(vToday,-1); YearEnd(vToday,-1)
Last 12 Months; MonthStart(vToday,-12); MonthEnd(vToday,-1)
Prev. MTD; addyears(monthstart(vToday),-1);addyears(addmonths(vToday,0),-1)
Prev. YTD; YearStart(vToday,-1); addyears(vToday,-1)
] (delimiter is ';')
;
JOIN (Ranges)
IntervalMatch (Date)
LOAD
RangeStart, RangeEnd
RESIDENT Ranges
;
DROP FIELDS RangeStart, RangeEnd
;
LEFT KEEP (TimeLine)
DateGrouping:
LOAD
*
RESIDENT Ranges
;
DROP TABLE Ranges

//I hope this helps!

//Best Wishes

//Mike Sikora

[santhana]

Dear Mike

Thanks for your reply.  I will try and let you know. Hope we can also make the range or Quarter like it has been made for month, week, year etc..

Thanks & Regards

Santhana

[michaelsikora]

Hi Santhana,

You might want to look into the master calendar, with the link below: Master Calendar

Please let me know if this response helps you!

Thanks,

Mike