Adding values from multiple arrays

[shonac]

Hi,

I am trying to add the values from 2 array functions to get the overall sum.

| eval {1_month_last_day_prior} = case((StartDateEpoch < '1_month_last_day_prior_epoch') AND (StartDateEpoch > '2_month_last_day_prior_epoch'), additionalBillingRate2,
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'), additionalBillingRate2,
StartDateEpoch > '2_month_last_day_prior_epoch', "0",1=1,MRR)

The below is not working and im not sure what syntax I should be using:

| eval sum='{1_month_last_day_prior}' + '{2_month_last_day_prior}'

Any ideas?

[renjith_nair]

1_month_last_day_prior,2_month_last_day_prior - are these existing field values (e.g. | eval {aName}=aValue ) or a new variable?

If it's a new variable, you dont need the { } brackets around them

If it's an existing field value as mentioned in Eval Field_names , then it will not work in this way

[shonac]

Hi @renjith_nair ,

These are existing fields I have created here:

| 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")

Is there any other way I could add the values?

This is how the results look currently, trying to add the 3 values.

[renjith_nair]

If you just want to add those row values and create a new field , just add this

| addtotals row=t

or just lets know the expected output.

[shonac]

The issue with this is I have other values in each row, but I only want to sum the first 3 dates:

[renjith_nair]

Is it always the first 3 dates ? You may use foreach  with some conditions. 

e.g.

your search|eval total=0,count=0
|foreach 20* [eval total=if(count < 3,total+<<FIELD>>,total+0)|eval count=count+1]

Is it possible to share your current search , you may mask sensitive information if any 

[shonac]

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*