Expression

[varunreddy]

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

[swuehl · Accepted solution]

Maybe like

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

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

[varunreddy]

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

[swuehl]

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.

[varunreddy]

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

[sunny_talwar]

What about this:

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

[swuehl]

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?

[varunreddy]

Hi Sunny,

Thanks for the response.

Response provided by Swuel is working fine

Regards,

Varun

[sunny_talwar]

Great, his responses are like that

[varunreddy]

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