topic Re: Expression error in QlikView

Expression error

Wed, 25 Nov 2020 16:16:04 GMT

Dear experts,

I have following 2 expressions which are not working properly,

01. NUM#(TODAY(DATE#(1))-DATE#(DATE))

Sub total is not working for this expression let me know the mistake made by me.

02. SUM(IF(STATUS='ACTIVE',((LOAN_AMOUNT*(RATE/22.54))-(TODAY(DATE#(1))-DATE#(DATE))),((INS_AMT*NO_INS)-LOAN_AMOUNT)))

Under lined part of the expression is not working. when i test only this part it's working.

Thks & Rgds,

Priyantha.

Re: Expression error

marcus_sommer — Tue, 16 Aug 2016 05:38:29 GMT

Try this:

NUM(TODAY()-DATE(DATE))

or if DATE isn't interpreted as date

NUM(TODAY()-DATE(DATE#(DATE), 'YourFormatPattern'))

and of course the same logic within the second expression.

- Marcus

Re: Expression error

Tue, 16 Aug 2016 06:08:45 GMT

Dear Marcus,

Thanks lot for early answer, But same error still available,

Thkx & Rgds,

Priyantha,

Re: Expression error

marcus_sommer — Tue, 16 Aug 2016 06:11:12 GMT

How looked DATE - values and formattings?

- Marcus

Re: Expression error

Tue, 16 Aug 2016 06:17:04 GMT

I want to take the no. of days (Value) between two days.

Priyantha.

Re: Expression error

jonathandienst — Tue, 16 Aug 2016 06:31:24 GMT

>>01. NUM#(TODAY(DATE#(1))-DATE#(DATE))

There is both a logical error as well as a naked field* (bold) in this expression. The logic should be as Marcus outlined above, but that does not solve the naked field. It is not clear what you want to with the total for this expression.

>>02. SUM(IF(STATUS='ACTIVE',((LOAN_AMOUNT*(RATE/22.54))-(TODAY(DATE#(1))-DATE#(DATE))),((INS_AMT*NO_INS)-LOAN_AMOUNT)))

Let's rewrite this in a way that is more readable, and fix the incorrect use of Today():

SUM(IF(STATUS='ACTIVE',

LOAN_AMOUNT * RATE / 22.54 - (TODAY() - DATE#(DATE)),

INS_AMT * NO_INS - LOAN_AMOUNT))

But that looks logically wrong as well - you are subtracting a number of days from a value (LOAN_AMOUNT * RATE), which does not look correct - do you not mean to divide?

*Naked field is a reference to a field not inside an aggregation function like Sum(), Count(), Max() etc. This field will only return a value if there is only one possible value at that point - which is not true for the totals.

Re: Expression error

Tue, 16 Aug 2016 07:26:16 GMT

Dear Jonathan,

Thanks for the early reply,

I want to calculate the loan interest for the number of days between today and the Started day(DATE). Rate should be divided from 22.54 to achieve the daily basis interest rate.

In this simple logic available to calculate

If( Status= 'ACTIVE',

If above logic is true,

1. loan_amount*Daily Rate* no.of days,

Other wise,

2. (Installment* no. of installment)-loan_amount.

The problem is no.of days calculation is not working with the logic as i understood. As i have tested the same without logic it is working as expected.

Thnx & Rgds,

Priyantha.

Re: Expression error

marcus_sommer — Tue, 16 Aug 2016 08:19:10 GMT

I think the problem is that DATE isn't unique defined and you need something like:

NUM(TODAY()-max(DATE))

which then needs to be wrapped with an aggr because it would be inside from the sum-function (no nested aggregation without an aggr-function) whereby it might be easier to outsource these calculation into a variable.

- Marcus