Hi @renjith_nair , It will always be the first 3 dates I need to add as these are for the prior period (PPadjustment). This is my full search, apologies it is extremely long. | inputcsv gen_billable_conns.csv
| search Client="*"
| lookup gen_billing_discount_rates.csv ClientName as Client OUTPUT Premium, Discount
| fillnull value=1 Premium, Discount
| eval StartDateEpoch=strptime('Client Handover Date', "%Y-%m-%d"), DecomDateEpoch=strptime('Decommission Date', "%Y-%m-%d")
| eval StartYear=strftime(StartDateEpoch, "%Y"), StartMonth=strftime(StartDateEpoch, "%b"), StartDay=strftime(StartDateEpoch, "%d"), DecomYear=strftime(DecomDateEpoch, "%Y"), DecomMonth=strftime(DecomDateEpoch, "%b"), DecomDay=strftime(DecomDateEpoch,"%d")
| eval billingDate="01-10-2020", billDateEpoch=strptime(billingDate, "%d-%m-%Y")
| eval jiraMRR=MRR
| eval 1_month_last_day=strftime(relative_time('billDateEpoch' ,"@mon-1d"), "%d")
| eval 2_month_last_day=strftime(relative_time('billDateEpoch' ,"-1mon@mon-1d"), "%d")
| eval 3_month_last_day=strftime(relative_time('billDateEpoch' ,"-2mon@mon-1d"), "%d")
| eval totalDaysInPreviousQuarter='1_month_last_day'+'2_month_last_day'+'3_month_last_day'
| eval daysBetweenHandoverAndBill=round((billDateEpoch-StartDateEpoch)/60/60/24, 0), daysBetweenDecomAndBill=round((billDateEpoch-DecomDateEpoch)/60/60/24, 0)
| eval adjustedMRR=if(daysBetweenHandoverAndBill<=365, MRR*Premium, MRR*Discount)
| eval adjustedBillingRate=exact(adjustedMRR*3)
| eval requiresAdjustedMRR=if('Deviation from Base MRR'="Yes", "Yes", "No")
| eval MRR=if(requiresAdjustedMRR="Yes", adjustedMRR, MRR)
| eval DRR=(MRR*3/totalDaysInPreviousQuarter)
| eval normalBillingRate=exact(MRR*3)
| eval additionalBillingRate=exact((DRR*daysBetweenHandoverAndBill)+NRR)
| eval billingCredit=exact(DRR*daysBetweenDecomAndBill)
| eval requiresAdditionalBillingRate=if(daysBetweenHandoverAndBill <= totalDaysInPreviousQuarter, "Yes", "No")
| eval requiresBillingCredit=if(daysBetweenDecomAndBill <= totalDaysInPreviousQuarter, "Yes", "No")
| eval billingCharge=if(requiresAdditionalBillingRate="Yes", (normalBillingRate+additionalBillingRate), normalBillingRate)
| eval billingCharge=if(requiresAdjustedMRR="Yes" AND requiresAdditionalBillingRate="Yes", (adjustedBillingRate+additionalBillingRate), 'billingCharge')
| eval billingCharge=if(billingCredit>0, 0,'billingCharge')
| fillnull value=0 billingCredit
| eval QCharge=billingCharge-billingCredit
| eval NRR=if(requiresAdditionalBillingRate="Yes", NRR, 0)
| rename billingCharge as quarterlyCharge, billingCredit as quarterlyCredit, QCharge as totalCost
| eval 1_month_last_day_prior=strftime(relative_time('billDateEpoch' ,"@mon-1d"), "%Y-%m-%d")
| eval 2_month_last_day_prior=strftime(relative_time('billDateEpoch' ,"-1mon@mon-1d"), "%Y-%m-%d")
| eval 3_month_last_day_prior=strftime(relative_time('billDateEpoch' ,"-2mon@mon-1d"), "%Y-%m-%d")
| eval 1_month_last_day_current=strftime(relative_time(billDateEpoch,"+1mon@mon-1d"), "%Y-%m-%d")
| eval 2_month_last_day_current=strftime(relative_time(billDateEpoch,"+2mon@mon-1d"), "%Y-%m-%d")
| eval 3_month_last_day_current=strftime(relative_time(billDateEpoch,"+3mon@mon-1d"), "%Y-%m-%d")
| eval 1_month_last_day_prior_epoch=relative_time('billDateEpoch' ,"@mon-1d")
| eval 2_month_last_day_prior_epoch=relative_time('billDateEpoch' ,"-1mon@mon-1d")
| eval 3_month_last_day_prior_epoch=relative_time('billDateEpoch' ,"-2mon@mon-1d")
| eval 4_month_last_day_prior_epoch=relative_time('billDateEpoch' ,"-3mon@mon-1d")
| eval 1_month_last_day_current_epoch=relative_time(billDateEpoch,"+1mon@mon-1d")
| eval 2_month_last_day_current_epoch=relative_time(billDateEpoch,"+2mon@mon-1d")
| eval 3_month_last_day_current_epoch=relative_time(billDateEpoch,"+3mon@mon-1d")
| eval 4_month_last_day_current_epoch=relative_time(billDateEpoch,"+4mon@mon-1d")
| eval start_month_last_day_epoch=relative_time(StartDateEpoch,"+1mon@mon-1d")
| eval decom_month_last_day_epoch=relative_time(StartDateEpoch,"+1mon@mon-1d")
| eval daysBetweenHandoverAndEOM=round(('start_month_last_day_epoch'-StartDateEpoch)/60/60/24, 0), daysBetweenDecomAndEOM=round(('decom_month_last_day_epoch'-DecomDateEpoch)/60/60/24, 0)
| eval additionalMonthlyRate=round((DRR*daysBetweenHandoverAndEOM),2), creditMonthlyRate=round((DRR*daysBetweenDecomAndEOM),2)
| eval {1_month_last_day_prior} = case((StartDateEpoch < '1_month_last_day_prior_epoch') AND (StartDateEpoch > '2_month_last_day_prior_epoch'), additionalMonthlyRate,
StartDateEpoch > '1_month_last_day_prior_epoch', "0",1=1,MRR)
| eval {2_month_last_day_prior} = case((StartDateEpoch < '2_month_last_day_prior_epoch') AND (StartDateEpoch > '3_month_last_day_prior_epoch'), additionalMonthlyRate,
StartDateEpoch > '2_month_last_day_prior_epoch', "0",1=1,MRR)
| eval {3_month_last_day_prior} = case((StartDateEpoch < '3_month_last_day_prior_epoch') AND (StartDateEpoch > '4_month_last_day_prior_epoch'), additionalMonthlyRate,
StartDateEpoch > '3_month_last_day_prior_epoch', "0",1=1,MRR)
| eval {1_month_last_day_current} = case((StartDateEpoch < '1_month_last_day_current_epoch') AND (StartDateEpoch > '1_month_last_day_prior_epoch'), additionalMonthlyRate,
StartDateEpoch > '1_month_last_day_current_epoch', "0",1=1,MRR)
| eval {2_month_last_day_current} = case((StartDateEpoch < '2_month_last_day_current_epoch') AND (StartDateEpoch > '1_month_last_day_current_epoch'), additionalMonthlyRate,StartDateEpoch > '2_month_last_day_current_epoch', "0",1=1,MRR)
| eval {3_month_last_day_current} = case((StartDateEpoch < '3_month_last_day_current_epoch') AND (StartDateEpoch > '2_month_last_day_current_epoch'), additionalMonthlyRate,StartDateEpoch > '3_month_last_day_current_epoch', "0",1=1,MRR)
| table "Circuit ID", Key, Summary, "Billing Notes", NRR, MRR, "Client Handover Date", "Decommission Date", quarterlyCharge, quarterlyCredit, totalCost, Currency, PPadjustment, 20*
... View more
