topic Re: Scripting in QlikView

Scripting

powerqlik — Wed, 30 Aug 2017 18:54:27 GMT

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

Re: Scripting

sunny_talwar — Wed, 30 Aug 2017 19:12:53 GMT

May be this

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

Re: Scripting

powerqlik — Wed, 30 Aug 2017 19:26:50 GMT

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

Re: Scripting

sunny_talwar — Wed, 30 Aug 2017 19:29:47 GMT

What is the date field in your application?

Re: Scripting

powerqlik — Wed, 30 Aug 2017 19:36:56 GMT

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

Re: Scripting

sunny_talwar — Wed, 30 Aug 2017 19:43:18 GMT

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

powerqlik — Wed, 30 Aug 2017 19:58:08 GMT

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

powerqlik — Wed, 30 Aug 2017 21:33:11 GMT

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

sunny_talwar — Thu, 31 Aug 2017 01:45:09 GMT

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

powerqlik — Thu, 31 Aug 2017 03:09:42 GMT

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

Re: Scripting

sunny_talwar — Thu, 31 Aug 2017 11:22:48 GMT

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

Re: Scripting

powerqlik — Thu, 31 Aug 2017 15:56:21 GMT

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

powerqlik — Tue, 05 Sep 2017 16:15:06 GMT

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

Peter_Cammaert — Tue, 05 Sep 2017 16:24:31 GMT

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

Re: Scripting

powerqlik — Tue, 05 Sep 2017 16:42:09 GMT

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

sunny_talwar — Tue, 05 Sep 2017 16:44:27 GMT

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

powerqlik — Tue, 05 Sep 2017 17:06:26 GMT

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

sunny_talwar — Tue, 05 Sep 2017 17:09:31 GMT

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

Re: Scripting

powerqlik — Tue, 05 Sep 2017 17:31:32 GMT

Yup, that is correct. Please see screenshot.

Re: Scripting

sunny_talwar — Tue, 05 Sep 2017 17:37:07 GMT

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

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample