Complex UDDC Expressions - Examples

Modified on Tue, 2 Aug, 2022 at 3:02 PM

Sr No	Input	Output	Expression	Description	Example	Function Category
1	Gross Sales - Double e.g 70 Target - 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-2020 End 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-2022 End 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. 35000 Monthly Target - e.g. 25000 Working Days - e.g. 28 Incentive 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 - 1000 Evaluated - 850 CoveragePercentage = 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 month Cell reference for previous month transaction count With 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 Timestamp Eg: 4 Feb 13:00:00 Column 2: Outage End Timestamp Eg: 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 outage starts on 4th February at 13:00:00 and ends on 5th February at 19:00:00 then the total outage seconds will be 89,999. Note: Here the assumption is that the service starts at 5:00:00 and ends at 23:59:59. Hence, outage has been calculated accordingly	Case Statement & Date
9	Month sale - e.g. 35000 Previous month sale - e.g. 25000	Result: Sales variance of current month sales from last month sales e.g. 60%	((month sale - previous month sale)/ previous month sale) * 100	Calculate sales variance of current month from the last month	If sales of last month is $100 and sales of current month is $50 then the sales variance will be $50 and -50%	Arithmetic