22 Replies Latest reply: Sep 5, 2017 2:06 PM by ALEN ARUTYUNYAN

# Scripting

The script below calculates the total lease obligation.  I want to be able to modify this script so that it would calculate the total remaining lease obligation as of this month or as of today.

(sum({\$<LocalLeaseTypeExpenses = {Lease}, Expense = {"*"}>} Amount))

• ###### Re: Scripting

May be this

Sum({\$<LocalLeaseTypeExpenses = {Lease}, Expense = {"*"}, Date = {"\$(='>=' Date(Today(), 'MM/DD/YYYY'))"}>} Amount)

• ###### Re: Scripting

For some reason, it's not recognizing the date.  Please see attached.

• ###### Re: Scripting

What is the date field in your application?

• ###### Re: Scripting

Thanks for the prompt feedback!  Please see a screenshot.  I want to make sure you have all the necessary info.

• ###### Re: Scripting

It seems that you need this based on the ExpenseMonth, is that right? If that is true, do you know if ExpenseMonth is a date field? Meaning it has a underlying Numerical value? How do you create ExpenseMonth in the script?

• ###### Re: Scripting

Hi Sunny, My goal is to be able to calculate the remaining expenses as of this month (i.e. 24 month lease.  10 months left.  How much is the remaining 10 months of expense equate to as of this month.

The script below calculates all of the expenses for the entirety of the lease (i.e. 24 months)

(sum({\$<LocalLeaseTypeExpenses = {Lease}, Expense = {"*"}>} Amount))

ExpenseMonth is calculated by taking the right side of the expense column shown above in the above excel sheet and counting 3 characters (i.e Jan, Feb)

Hope that helps and thank you!

• ###### Re: Scripting

Maybe this can help.  I have managed to create the formula below that gives me the total lease expense less this month, this year (meaning it gives me total lease expense minus this month (only one month).

In order to calculate the remaining lease obligation, maybe you can help me tweak it to be all remaining lease expenses after this month this year (i.e greater than >?)

(sum({\$<LocalLeaseTypeExpenses = {Lease}, Expense = {"*"}>} Amount))-sum({\$<LocalLeaseTypeExpenses = {Lease}, Expense = {"*"}, ExpenseMonth = {\$(vThisMonth)}>} if(ExpenseYear = \$(vThisYear), Amount))

• ###### Re: Scripting

In this table, create a new field like this

Date(MonthStart(Date#(Period, 'YYYY - MMM')), 'YYYY-MMM') as ExpenseMonthYear

and then try this

Sum({\$<LocalLeaseTypeExpenses = {Lease}, Expense = {"*"}, ExpenseMonthYear = {"\$(='>' Date(Today(), 'YYYY-MMM'))"}>} Amount)

• ###### Re: Scripting

I think we are getting closer as it accepted the script but is displaying 0 for the pivot table.  Thanks for helping!

• ###### Re: Scripting

Would you be able to post a screenshot of ExpenseMonthYear field in a list box object?

• ###### Re: Scripting

Sure, please see attached.  It doesn't seem to recognize the formula right after {"*"},   which is odd because it doesn't error out the formula in the pivot table (please see above), it just displays a 0.  With that being said, the amount in the list box now displays the total of the entire lease (i.e. 24 months or 36 months, etc)

• ###### Re: Scripting

Hi Sunny, I just wanted to follow up and see if there was anything else I may be able to do to correct the script?  Thanks in advance!

• ###### Re: Scripting

Maybe provide what he asked for: "a screenshot of ExpenseMonthYear field in a list box object"?

• ###### Re: Scripting

Peter,

I did reply to his request on 8/31/17 with a screenshot and an explanation that it does not seem to recognize the script after {"*"}, (please see above).

Attached is another screenshot.  The amount shown for Washington is the total expense for the entire term, not as of this month.  Thanks.

• ###### Re: Scripting

Do you have a field called ExpenseMonthYear in your dashboard? It appears as if you don't.... Did you reload your dashboard after you added ExpenseMonthYear to your script?

• ###### Re: Scripting

Hi Sunny, I may have not reloaded the data because after reloading, ExpenseMonthYear now became a field.  With that being said, even though it recognizes the formula, it does not display the correct response as now it reflects the total expense as 0.  Please see attached and thanks for spending so much time on this trying to help me get it right..

• ###### Re: Scripting

and you are 100% sure that there are period values beyond September?

• ###### Re: Scripting

Yup, that is correct.  Please see screenshot.

• ###### Re: Scripting

Don't really know... would you be able to share your qvw?

Preparing examples for Upload - Reduction and Data Scrambling