Qlik Sense Load Script

 

Creating an ageing profile:

Based on the field ‘CREATEDDATETIME’

 

LET vTable=’TABLE_NAME’;

LET vPrefix = upper(replace( ‘$(vTable)’,’_’,’ ‘))

 

today() – date(floor(CREATEDDATETIME)) AS [$(vPrefix) Age (Days)]

 
If( [$(vPrefix) Age (Days)] <0, dual(‘Forward Dated’,1),
If([$(vPrefix) Age (Days)] >=0 and [$(vPrefix) Age (Days)] <=7, dual(‘0-7 Days’,2),
If([$(vPrefix) Age (Days)] >7 and [$(vPrefix) Age (Days)] <=14, dual(‘8-14 Days’,3),
If([$(vPrefix) Age (Days)] >14 and [$(vPrefix) Age (Days)] <=21, dual(’15-21 Days’,4),
If([$(vPrefix) Age (Days)] >21 and [$(vPrefix) Age (Days)] <=28, dual( ’22-28 Days’,5),
If([$(vPrefix) Age (Days)] >28 and [$(vPrefix) Age (Days)] <=60, dual( ‘1-2m’,6),
If([$(vPrefix) Age (Days)] >60 and [$(vPrefix) Age (Days)] <=90, dual( ‘2-3m’,7),
If([$(vPrefix) Age (Days)] >90 and [$(vPrefix) Age (Days)] <=180, dual(‘3-6m’,8),
If([$(vPrefix) Age (Days)] >180 and [$(vPrefix) Age (Days)] <=365, dual(‘6-12m’,9),
If([$(vPrefix) Age (Days)] >365 , dual(’12m+’,10),

‘ ‘))))))))))

AS [$(vPrefix) Age Group],

Qlik Sense & Excel Help Site

%d bloggers like this: