Discussion Board for collaboration related to QlikView App Development.
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
Mapp_Qtr:MappingLoadMobile_No,'Repeat'From[C:\.......\QVD\Final\Final.qvd](qvd) where Date<=QuarterStart(today());
LOADLead,Status,Quarter,Key,Month,if( Date>=QuarterStart(today()) and Date<=QuarterEnd(today()), ApplyMap('Mapp_Qtr',Mobile_No,'Fresh')) as Lead_Type,
//Let me know if more information is required. Thanks in advance
Thanks & Regards
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:LOADRange,date(evaluate(RangeStart)) as RangeStart,date(evaluate(RangeEnd)) as RangeEnd;LOAD * INLINE [Range; RangeStart; RangeEndToday; 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)LOADRangeStart, RangeEndRESIDENT Ranges;DROP FIELDS RangeStart, RangeEnd;LEFT KEEP (TimeLine)DateGrouping:LOAD*RESIDENT Ranges;DROP TABLE Ranges
//I hope this helps!
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..
You might want to look into the master calendar, with the link below: Master Calendar
Please let me know if this response helps you!