Complex UDDC Expressions - Examples

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

Sr NoInputOutputExpressionDescriptionExampleFunction Category
1Gross 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
2Seconds - Int e.g 4500Result : 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
3Start Date - Date e.g 01-Feb-2020
End Date - Date  e.g 01-Jun-2020
Result : e.g 59 daysifCase(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
4Start Date - Date e.g 01-Jan-2022
End Date - Date  e.g 26-Apr-2022
Result : e.g Ageing Bucket = 61 to 120 daysifCase( (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 dateFor 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
5Achievement - e.g. 35000
Monthly Target - e.g. 25000
Working Days - e.g. 28
Incentive Perc - e.g. 10%
Result : e.g.  Incentive Per Day = 36RsifCase( 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
6Total - 1000
Evaluated - 850
CoveragePercentage = 85%
Result: One, Two, Three... SixteenifCase(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 coverageCase Statement
7Transaction count current month
Cell reference for previous month transaction count
With month as a column dimension in crosstab
Result: 46%((txns - R1C6) / R1C6) * 100Calculate growth for a month as compared to previous month taking values of previous month from cell reference
8Column 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,999ifCase(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 secondsFor 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
9Month 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) * 100Calculate 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

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article