# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
New Contributor III

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

Tags (2)
22 Replies
MVP

## Re: Scripting

May be this

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

New Contributor III

## Re: Scripting

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

MVP

## Re: Scripting

What is the date field in your application?

New Contributor III

## Re: Scripting

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

MVP

## 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?

New Contributor III

## 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!

New Contributor III

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

MVP

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

New Contributor III

## Re: Scripting

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