Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

To make a logic dynamic

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

3 Replies
Highlighted
Contributor III
Contributor III

Re: To make a logic dynamic

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

Highlighted
Contributor III
Contributor III

Re: To make a logic dynamic

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

Highlighted
Contributor III
Contributor III

Re: To make a logic dynamic

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