Using expression in pick function

Question

I have tried Num((today()-I_TRAN_DATE)/90 + 1,0) individually and it will return integer, but it seems not working when I try to combined it with pick function. I know it's not finished but should at least return result for 1-3

pick(
    Num((today()-I_TRAN_DATE)/90 + 1,0)
    ,'less than 3 months'
    ,'3-6 months'
    ,'6-12 months'
    ,'greater than 1 year'
    )

score 1 · Answer 1 · answered Jan 29 '16 at 09:43

This looks like an issue with passing the number from the expression to the pick function. When using the num function, this does not change the underlying value - including a few more brackets and a round function resolves the issue as per the below script which generates a list of dates and then applies the pick function at the end.

Let varMinDate = Num(31350);  //30/10/1985
Let varMaxDate = Num(42400);  //31/01/2016

TempCalendar:  
LOAD  
               $(varMinDate) + Iterno()-1 As Num,  
               Date($(varMinDate) + IterNo() - 1) as TempDate  
               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);  

TestCalendar:  
Load  
               TempDate AS I_TRAN_DATE,  
               week(TempDate) As Week,  
               Year(TempDate) As Year,  
               Month(TempDate) As PeriodMonth,  
               Day(TempDate) As Day,  
               if(Year2Date(TempDate),1,0) as CurYTDFlag,  
               if(Year2Date(TempDate,-1),1,0) as LastYTDFlag,  
               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,  
               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,  
               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,  
               WeekDay(TempDate) as WeekDay,
               if(TempDate>=MonthStart(AddMonths(Today(),-12)) and(TempDate<=MonthStart(Today())),1,0) as Rolling12Month  
               ,'Q' & Ceil (Month(TempDate)/3) as Quarter 
               ,Year(TempDate)&'-Q' & Ceil (Month(TempDate)/3) as YearQuarter
               ,if(Year(TempDate)=(Year(Today())-1),1,0) as LastYear
               , if(MonthStart(TempDate)=MonthStart(Today()),null(),'Exclude Current Period') As ExcludeCurrentPeriod
Resident TempCalendar  
Order By TempDate ASC;  
Drop Table TempCalendar;  

Let varMinDate = null();
Let varMaxDate = null();


PeriodTable:
Load 
Pick(round((num((((today()-I_TRAN_DATE)/90) + 1),0)),1)
,'less than 3 months'
,'3-6 months'
,'6-12 months'
,'greater than 1 year') as Period
,I_TRAN_DATE
Resident TestCalendar;

Using expression in pick function

1 Answers1