Useful Formulas

Finding Strings:

CASE WHEN {jobmain.jobname} LIKE ‘%whatever%’ AND {item} LIKE ‘%abcitem%’ THEN ‘whatever contains abcitem’ END

 

 

Month to Date:

case when {trandate} >= trunc({today},’MONTH’)  then ({quantity}) end

Last Month:

case when {trandate} BETWEEN add_months(trunc({today},’MONTH’),-1) AND trunc({today},’MONTH’) then ({quantity}) end

Six Month Ago

case when {trandate} BETWEEN add_months(trunc({today},’MONTH’),-6) AND add_months(trunc({today},’MONTH’),-5)-1 then ({quantity}) end

Six Month Average

case when {trandate} BETWEEN add_months(trunc({today},’MONTH’),-6) AND trunc({today},’MONTH’) then round(({quantity})/6,2) end