topic Re: Expression in QlikView

Expression

varunreddy — Wed, 30 Dec 2015 17:29:51 GMT

Hi Guys,

I am trying to achieve commentary, where maximum [commentary date] less than or equal to maximum [As of Date].

i.e. only({<max([commentary date] = {'<= $(vmaxDate)'}>}Commentary).

Can you please correct the expression

Re: Expression

swuehl — Wed, 30 Dec 2015 17:36:27 GMT

Maybe like

=FirstSortedValue({<[commentary date] = {"<=$(vmaxDate)"}>} Commentary, -[commentary date])

You need to take care that your variable matches the format of commentary date field.

Re: Expression

varunreddy — Wed, 30 Dec 2015 17:56:31 GMT

Hi Swuehl,

Thanks for your quick response.

I tried this.

FirstSortedValue({<[Commentary Date] = {"<= $(vtest)"}>}Commentary, -[Commentary Date])

It is not displaying any output.

Thanks in advance!

Cheers

Re: Expression

swuehl — Wed, 30 Dec 2015 18:04:37 GMT

If there are more than one Commentary for a given maximum date, FirstSortedValue() will return NULL.

Could this be the case?

You can add a DISTINCT qualifier to get a Commentary returned also in these cases:

FirstSortedValue({<[Commentary Date] = {"<= $(vtest)"}>} DISTINCT Commentary, -[Commentary Date])

Then, please double check if your variable is returning the value you expect and if the format matches the field format. To check the variable expansion, use above expression e.g. in a straight table expression with no expression label set, then hover with the mouse over the label,this should show the expression itself with all expansions.

Re: Expression

varunreddy — Wed, 30 Dec 2015 18:07:17 GMT

I have two Commentary Date,

11/28/2015 and 11/29/2015.

The expression mentioned above should display data only for 11/29/2015?

Regards,

Varun

Re: Expression

sunny_talwar — Wed, 30 Dec 2015 18:11:18 GMT

What about this:

FirstSortedValue({<[Commentary Date] = {"$(='<=' & $(vtest))"}>}Commentary, -[Commentary Date])

Re: Expression

swuehl — Wed, 30 Dec 2015 18:11:57 GMT

It should return only the Commentary for the max possible commentary date. Your dates need to have a numeric representation (like all dates should have), i.e. the should be generated using a QV date function or read in with correct default date format or date#() interpretation function.

If it's still not working, could you upload a small sample QVW?

Re: Expression

varunreddy — Wed, 30 Dec 2015 18:13:59 GMT

Hi Sunny,

Thanks for the response.

Response provided by Swuel is working fine

Regards,

Varun

Re: Expression

sunny_talwar — Wed, 30 Dec 2015 18:16:27 GMT

Great, his responses are like that

Re: Expression

varunreddy — Tue, 12 Jan 2016 15:44:15 GMT

Hi Swuehl,

In the expression mentioned below:

FirstSortedValue({<[commentary date] = {"<=$(vmaxDate)"}>} Commentary, -[commentary date])

For, [commentary date] = {"<=$(vmaxDate)"}>} ,

We should only get Commentary dates that are less than vmaxDate, but it is displaying all the dates (including the dates > vmaxDate.

Is there a way to do it?

swuehl‌

sunny‌

Regards,

Varun

Re: Expression

sunny_talwar — Tue, 12 Jan 2016 16:41:28 GMT

Can you check if this -> ='<=' & $(vmaxDate) is showing you the right date in the right format?

Re: Expression

varunreddy — Tue, 12 Jan 2016 16:49:25 GMT

Sunny,

Tried This

irstSortedValue({<[commentary date] = {"{'<=' & $(vMonth)}>} "}>} [Commentary Date], -[commentary date])

Date#(date([Commentary Date],'MM/DD/YYYY')) as [Commentary Date]
Date([As of Date TS],'MM/DD/YYYY hh:mm:ss') as [As of Date TS],
date(floor(timestamp([As of Date TS],'MM/DD/YYYY'))) as [As of Date],

vmaxDate = max({1}[As of Date])

Re: Expression

swuehl — Tue, 12 Jan 2016 16:51:06 GMT

Try

vmaxDate = Date(max({1}[As of Date]),'MM/DD/YYYY')

Re: Expression

sunny_talwar — Tue, 12 Jan 2016 16:53:37 GMT

Try something like this:

=FirstSortedValue({<[commentary date] = {"$(='<=' & $(vmaxDate))"}>} [commentary date], -[commentary date])

Date([Commentary Date]) as [Commentary Date]
TimeStamp([As of Date TS],'MM/DD/YYYY hh:mm:ss') as [As of Date TS],
Date(Floor[As of Date TS])) as [As of Date],

vmaxDate = Date(Max({1} [As of Date])

Re: Expression

varunreddy — Tue, 12 Jan 2016 17:01:03 GMT

It is giving two values

FirstSortedValue({<[Commentary Date] = {"<=$(vTest)"},[Commentary Type]= {'Scorecard'}>} [Commentary Date], -[Commentary Date])

vTest - =date(max({1}[As of Date]),'MM/DD/YYYY')

I should get 1/5/2016

Re: Expression

sunny_talwar — Tue, 12 Jan 2016 17:03:11 GMT

Do you have commentary date as dimension? You will need to remove that if you do.

Re: Expression

varunreddy — Tue, 12 Jan 2016 17:03:49 GMT

If I take out Commentary Date it is not displaying any output

Re: Expression

swuehl — Tue, 12 Jan 2016 18:27:27 GMT

Note that vTest is a date in 2016, so why do you only expect 01/05/2015 and not 09/30/2015?