Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
powerqlik
New Contributor III

Scripting

Hello fellow Qlikview users.  Can someone please help modify my script to be able to calculate remaining obligation expense?  Thanks in advance!

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

22 Replies

Re: Scripting

May be this

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

powerqlik
New Contributor III

Re: Scripting

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

Re: Scripting

What is the date field in your application?

powerqlik
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.Capture.JPG

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?

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

Capture.JPG

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

Re: Scripting

In this table, create a new field like this

Capture.PNG

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

and then try this

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

powerqlik
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!Capture.JPG