Scripting

[powerqlik]

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

[sunny_talwar]

May be this

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

[powerqlik]

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

[sunny_talwar]

What is the date field in your application?

[powerqlik]

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

[sunny_talwar]

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]

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!

[powerqlik]

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

[sunny_talwar]

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)

[powerqlik]

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