Sr No Input Output Expression Description Example Function Category 1 Gross Sales - Double e.g 70Target - Double - e.g 100 Performance - e.g 70 % ifCase( (GrossSales == 0 ) && (Target != 0 ) , 0,ifCase( (GrossSales != 0 ) && (Target == 0 ) , 100,ifCase( (GrossSales == 0 ) && (Target == 0 ) , 0,  ((GrossSales * 100) / Target)))) Find out the achievement percentage from actual and target values to calculate the performance. Case Statement 2 Seconds - Int e.g 4500 Result : Time in Hours (HH:MM:SS)e.g 1:15:0 round(floor((Seconds/3600)),0) + " : " +round(floor(mod(Seconds,3600)/60),0) + " :" +round(floor(mod(floor(mod(Seconds,3600)),60)),0) Convert the time given in seconds to hour format (HH:MM:SS). Arithmetic 3 Start Date - Date e.g 01-Feb-2020End Date - Date  e.g 01-Jun-2020 Result : e.g 59 days ifCase(month(\$From_Date\$)>=6,noOfMonthsByDate(\$From_Date\$, \$To_Date\$),ifCase(month(\$To_Date\$)<=3,noOfMonthsByDate(\$From_Date\$, \$To_Date\$),ifCase((month(\$From_Date\$)>=1     && month(\$From_Date\$)<=4) &&   month(\$To_Date\$)==4 ,noOfMonthsByDate(\$From_Date\$, \$To_Date\$)-1,ifCase(month(\$From_Date\$)==4  &&      month(\$To_Date\$)==5 ,noOfMonthsByDate(\$From_Date\$, \$To_Date\$)-2,ifCase(month(\$From_Date\$)==5     &&   month(\$To_Date\$)==5 ,noOfMonthsByDate(\$From_Date\$, \$To_Date\$)-1,ifCase(month(\$From_Date\$)>=5     &&   month(\$To_Date\$)<=12 ,noOfMonthsByDate(\$From_Date\$, \$To_Date\$)-1,ifCase(month(\$From_Date\$)>=1 &&   (month(\$To_Date\$)>=5 && month(\$To_Date\$)<=12) ,noOfMonthsByDate(\$From_Date\$, \$To_Date\$)-2,0)))))    )) Calculate the total number of days between two dates excluding April and May from the date range. For Ex -  if the start date is 01-Feb-2020 and the end date is 01-Jun-2020, then the total number of days should be 59 days excluding Apr and May. Case Statement 4 Start Date - Date e.g 01-Jan-2022End Date - Date  e.g 26-Apr-2022 Result : e.g Ageing Bucket = 61 to 120 days ifCase( (dateDiff( "d", today(), StockDate)) >= 0 &&(dateDiff( "d", today(), StockDate)) <=60, "0 to 60 Days",ifCase( (dateDiff( "d", today(), StockDate)) >= 61 &&(dateDiff( "d", today(), StockDate)) <=120, "61 to 120 Days",ifCase( (dateDiff( "d", today(), StockDate)) >= 121 &&(dateDiff( "d", today(), StockDate)) <=180, "121 to 180 Days",ifCase( (dateDiff( "d", today(), StockDate)) >= 181 &&(dateDiff( "d", today(), StockDate)) <=365, "181 to 365 Days","More than 365 Days")))) Create custom aging values from date dimension w.r.t today’s date For Ex. - ‘Item1’ having batch ‘BT003’ is in stock since 1stJan2022. Hence, we can say that it is in stock from more than 61 days and less than 120 days considering today’s date as 26th Apr 2022. Case Statement 5 Achievement - e.g. 35000Monthly Target - e.g. 25000Working Days - e.g. 28Incentive Perc - e.g. 10% Result : e.g.  Incentive Per Day = 36Rs ifCase( Achievement >= MonthlyTarget, (((Achievement - MonthlyTarget)/WorkingDays)*\$IncentivePerc\$)/100, 0) Calculate the incentive earned per Day with respect to the given metrics values. For example - If the achievement amount is 35000, the Monthly Target is 25000, the number of working days in the particular month taken is 28 and the incentive is being calculated by 10%, then the Incentive so earned is of Rs. 36 per Day. Case Statement 6 Total - 1000Evaluated - 850CoveragePercentage = 85% Result: One, Two, Three... Sixteen ifCase(CoveragePercentage<=5,"One",ifCase(CoveragePercentage>5 && CoveragePercentage<=10,"Two",ifCase(CoveragePercentage>10 && CoveragePercentage<=15,"Three",ifCase(CoveragePercentage>15 && CoveragePercentage<=20,"Four",ifCase(CoveragePercentage>20 && CoveragePercentage<=25,"Five",ifCase(CoveragePercentage>25 && CoveragePercentage<=30,"Six",ifCase(CoveragePercentage>30 && CoveragePercentage<=35,"Seven",ifCase(CoveragePercentage>35 && CoveragePercentage<=40,"Eight",ifCase(CoveragePercentage>40 && CoveragePercentage<=45,"Nine",ifCase(CoveragePercentage>45 && CoveragePercentage<=50,"Ten",ifCase(CoveragePercentage>50 && CoveragePercentage<=55,"Eleven",ifCase(CoveragePercentage>55 && CoveragePercentage<=60,"Twelve",ifCase(CoveragePercentage>60 && CoveragePercentage<=65,"Thirteen",ifCase(CoveragePercentage>65 && CoveragePercentage<=70,"Fourteen",ifCase(CoveragePercentage>70 && CoveragePercentage<=75,"Fifteen",ifCase(CoveragePercentage>75,"Sixteen","N/A")))))))))))))))) Calculate coverage Range based on 5 calculated for coverage Case Statement 7 Transaction count current monthCell reference for previous month transaction countWith month as a column dimension in crosstab Result: 46% ((txns - R1C6) / R1C6) * 100 Calculate growth for a month as compared to previous month taking values of previous month from cell reference 8 Column 1: Outage Start TimestampEg: 4 Feb 13:00:00Column 2: Outage End TimestampEg: 5 Feb 19:00:00 Outage Seconds Eg: 89,999 ifCase(OutageStartDate != OutageEndDate, ((datePart("d", OutageEndDate)- datePart("d", OutageStartDate) - 1) *68399)+ (86399 - ((hour(OutageStartTimestamp) * 60 *60)+ (minute(OutageStartTimestamp) * 60)+ second(OutageStartTimestamp)))+ (((hour(OutageEndTimestamp) * 60 *60)+ (minute(OutageEndTimestamp) * 60) + second(OutageEndTimestamp)) - 18000), Null) Calculate the total outage time of a service in seconds For example: If the outagestarts on 4th February at13:00:00 and ends on5th February at 19:00:00 thenthetotal outage seconds will be 89,999.Note: Here the assumption isthat the service starts at 5:00:00and ends at 23:59:59.Hence, outage has beencalculated accordingly Case Statement & Date 9 Month sale - e.g. 35000Previous month sale - e.g. 25000 Result: Sales variance of currentmonth sales from last monthsales e.g. 60% ((month sale - previous month sale)/ previous month sale) * 100 Calculate sales variance of current month from thelast month If sales of last month is \$100 andsales of current month is \$50 thenthe sales variance will be \$50 and-50% Arithmetic