topic Re: Expression is giving no result in QlikView

Expression is giving no result

sakshikaul — Wed, 25 Nov 2020 16:16:04 GMT

here in above image the datatype used in database is user defined ie Money. Now when I am writing an expression its not giving correct result. Following is the expression :-

=sum({< Sold_Date = {">=$(From)<=$(To)"} >}[inv value] )

can be seen in the below image

But if I am using expression as sum([inv value] its giving me correct answer

and for conversion I have used following in the script.

Date(vouch_date, 'YYYY-MM-DD') as Sold_Date,

Please help

Re: Expression is giving no result

tresB — Mon, 15 Oct 2018 07:11:07 GMT

Try:

=sum({< Sold_Date = {">=$(=Date(From, 'YYYY-MM-DD'))<=$(=Date(To, 'YYYY-MM-DD'))"} >}[inv value] )

Re: Expression is giving no result

arvind1494 — Mon, 15 Oct 2018 07:15:34 GMT

sum({< Sold_Date = {">=$(=date(From))<=$(=date(To))"} >}[inv value] )

Re: Expression is giving no result

sakshikaul — Mon, 15 Oct 2018 07:25:28 GMT

bOTH OF THEM ARE NOT GIVING ANY RESULT

Re: Expression is giving no result

arvind1494 — Mon, 15 Oct 2018 07:56:48 GMT

If possible then Please send your data

Re: Expression is giving no result

MindaugasBacius — Mon, 15 Oct 2018 08:15:14 GMT

Maybe:

=sum({< Sold_Date = {">=$(=Date($(=From), 'YYYY-MM-DD'))<=$(=Date($(=To), 'YYYY-MM-DD'))"} >}[inv value] )

Re: Expression is giving no result

lorenzoconforti — Mon, 15 Oct 2018 08:48:53 GMT

When you hover the mouse over the Sold_Date field in the table view, does it show as $date tag?

Re: Expression is giving no result

jyothish8807 — Mon, 15 Oct 2018 08:49:11 GMT

May be this:

=sum({< Sold_Date = {">=$(=Date($(From), 'YYYY-MM-DD'))<=$(=Date($(To), 'YYYY-MM-DD'))"} >}[inv value] )

Just check the out put of both the variables in a text box to be sure:

1. Date($(From), 'YYYY-MM-DD')

2. Date($(To), 'YYYY-MM-DD')

Br,

Re: Expression is giving no result

juraj_misina — Mon, 15 Oct 2018 08:59:13 GMT

You need to format your Sold_Date field as a date in script:

Date#(Sold_Date, 'YYYY-MM-DD') ad SoldDate

From the screenshot it seems that it is a text field, which makes it impossible to do > and < comparisons.

Hope this helps.

Juraj

Re: Expression is giving no result

sakshikaul — Mon, 15 Oct 2018 09:41:26 GMT

I am getting following result

Re: Expression is giving no result

juraj_misina — Mon, 15 Oct 2018 09:50:55 GMT

Well, you can format it back to your previous format

Date(Date#(Sold_Date, 'YYYY-MM-DD'), 'YYYY-MM-DD') ad Sold_Date

But the question is, is your expression now working? If yes, job done. If not, my assumption was wrong and we need to work further.

If the expression is not working, please go the the output table and expand the expression column so that we see what is in the label row. This should contain $(From) and $(To) variables evaluated, which can give us a hint...

Optionally, can you format those variables to YYYY-MM-DD format (or your Sold_Date to DD/MM/YYYY format)?

Juraj

Re: Expression is giving no result

sakshikaul — Mon, 15 Oct 2018 09:59:35 GMT

I have done following changes in the script

Date(Date#(vouch_date, 'YYYY-MM-DD'),'YYYY-MM-DD')as Sold_Date,

Re: Expression is giving no result

Anil_Babu_Samineni — Mon, 15 Oct 2018 10:41:06 GMT

I am taking from last discussion. You can try?

Date(Num#(vouch_date),'YYYY-MM-DD') as Sold_Date

Re: Expression is giving no result

lorenzoconforti — Mon, 15 Oct 2018 11:03:02 GMT

That doesn't work because the date field in your data is formatted differently (i.e. not 'YYYY-MM-DD'). Can you please open up your source file and check how the data in the field "vouch_date" is formatted? With the Date# function you are telling Qlikview that your source data is in the format 'YYYY-MM-DD' which is clearly not because the result data after the load is blank (according to the screenshot you've attached in your last post).

Re: Expression is giving no result

sakshikaul — Mon, 15 Oct 2018 11:07:30 GMT

Here the date is in the same format as mentioned n the script.

Re: Expression is giving no result

lorenzoconforti — Mon, 15 Oct 2018 11:21:27 GMT

The date format in your variables is different from your data. Use this in your script:

date(date#(vouch_date,'YYYY-MM-DD hh:mm:ss[.fff]'),'DD/MM/YYYY') as Sold_Date,

Re: Expression is giving no result

sakshikaul — Mon, 15 Oct 2018 12:20:52 GMT

This is giving no result

Re: Expression is giving no result

lorenzoconforti — Mon, 15 Oct 2018 12:45:40 GMT

can you provide a screenshot of the source data, not the qlikview tables. Excel, csv, etc. It would be good to understand in what format the data is coming into qlikview

Re: Expression is giving no result

sakshikaul — Tue, 16 Oct 2018 05:02:33 GMT

Re: Expression is giving no result

jyothish8807 — Tue, 16 Oct 2018 05:29:15 GMT

Try this:

date(floor(vouch_date),'YYYY-MM-DD') as Sold_Date,

then in UI try like this:

=sum({< Sold_Date = {">=$(=Date($(From), 'YYYY-MM-DD'))<=$(=Date($(To), 'YYYY-MM-DD'))"} >}[inv value] )

Just check the out put of both the variables in a text box to be sure:

1. Date($(From), 'YYYY-MM-DD')

2. Date($(To), 'YYYY-MM-DD')

Br,