Need help with max(date) logic

[#1]

Hi There,

I have the following scenario:

I have a set of client data which is from different months. What i have as front-end are different charts,static tables and some filters.What i need to do is apply a logic that when a user selects a fiscal year I will have the latest results for their company and if they select a specific month they will get the latest data up until this month.

Sample Data:

Lets say that our Fiscal Year starts November

Fiscal Year	Client	Month	Date	Value
FY14	Bank of America	Jan	Jan 10th	10
FY14	Bank of America	Jan	Jan 15th	9
FY14	Bank of America	Mar	Mar 10th	8

What i want is when the user selects FY14 they will see the latest value for the Fiscal Year which is 8th and it was recorded on Mar the 10th.If they select Jan FY14, they will see the value of 9 which at this time was the latest value that they had. If they click on Mar... they will see the value of 8th. Any help will be widely appreciated.

Thanks!

[swuehl]

Create a Date field that has a numeric representation (e.g. using QV date interpretation functions):

Get the Dates Right

Then your expression to get the latest Value should just be

=FirstSortedValue( Value, -Date)

[sunny_talwar]

You can look for more on FirstSortedValue at this link: Value Associated with Min/Max Value of Another Field (Front End Solution)

[#4]

Hi There,

I need an aggregate of the Value as I have multiple responses.

So I might have for FY14 Jan three responses in the same date for the same customer that i will need to aggregate.

=FirstSortedValue( Value, -Date)

The "Value" here will be a SUM from the data.

[swuehl]

Maybe like

=FirstSortedValue( Aggr( Sum(Value), Date), Aggr(-Date, Date) )

[#6]

Hi swuehl,

This worked but it is showing the value only for the latest date. How can i group the same thing but for multiple clients.

For Example

Client Id: 2    

Date: 21/08/2014

Value: 2

Client Id:3

Date: 21/08/2014

Value:5

Client Id:2

Date: 22/08/2014

Value:5

Total Value should be: Client ID 3 latest value plus Client ID 2 latest value = Total: 10

[swuehl]

Maybe like

=Sum( Aggr( FirstSortedValue( Aggr( Sum(Value), Client,  Date), Aggr(-Date, Client, Date) ), Client))

[#8]

When i tried what you mentioned above i got the value 0, changed it with count and it gives me the value for all dates not only for the latest. Here is the exact expression that I used:

=SUM( Aggr( FirstSortedValue( Aggr( SUM({$<NPS_Category = {'Promoter'}>}NPS_Category), organization_survey_respondent_id,  close_date_cal), Aggr(-close_date_cal, organization_survey_respondent_id, close_date_cal) ), organization_survey_respondent_id))

[swuehl]

Could you upload a small sample QVW or some sample input records?

[#10]

I hope that this helps. I much appreciate you looking into that.