topic Need help with max(date) logic in QlikView

Need help with max(date) logic

Tue, 17 Nov 2015 10:21:29 GMT

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!

Re: Need help with max(date) logic

swuehl — Tue, 17 Nov 2015 12:39:34 GMT

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)

Re: Need help with max(date) logic

sunny_talwar — Tue, 17 Nov 2015 13:04:59 GMT

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

Re: Need help with max(date) logic

Tue, 17 Nov 2015 18:52:04 GMT

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.

Re: Need help with max(date) logic

swuehl — Tue, 17 Nov 2015 20:04:25 GMT

Maybe like

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

Re: Need help with max(date) logic

Wed, 18 Nov 2015 13:04:45 GMT

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

Re: Need help with max(date) logic

swuehl — Wed, 18 Nov 2015 16:22:44 GMT

Maybe like

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

Re: Need help with max(date) logic

Wed, 18 Nov 2015 16:56:29 GMT

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))

Re: Need help with max(date) logic

swuehl — Wed, 18 Nov 2015 17:12:51 GMT

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

Re: Need help with max(date) logic

Thu, 19 Nov 2015 14:20:17 GMT

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

Re: Need help with max(date) logic

swuehl — Sat, 21 Nov 2015 12:06:59 GMT

You shouldn't do a SUM of the NPS_Category, because it's a text value, right?

Something like

=Sum({$<NPS_Category = {'Promoter'}>}

Aggr( FirstSortedValue(

Aggr( count({$<NPS_Category = {'Promoter'}>}NPS_Category), client_id, close_date_cal),

Aggr(-Only( {$<NPS_Category = {'Promoter'}>}close_date_cal), client_id, close_date_cal)

)

, client_id)

)

Seems to me returning the correct values. For example, with the selection made in attached sample, I get 210.

Summing all the count of NPS_Category for the last close_date_cal per client also gives me this number.

Re: Need help with max(date) logic

Mon, 23 Nov 2015 14:04:34 GMT

Yes, it is a text value, but what we should have in the end is... if you click on Fiscal Year as a filter and you filter by 2014 you should end up with 797 promoters.

Re: Need help with max(date) logic

swuehl — Mon, 23 Nov 2015 17:27:59 GMT

Would be helpful if you tell us how you have calculated the 797 promoters, or how the number of promoters correlates with the count of NPS_Category.

Re: Need help with max(date) logic

Tue, 01 Dec 2015 13:11:20 GMT

In the attached file i already grouped them by Fiscal Year and when i group the data by fiscal year i get the latest date and results on a Fiscal Year level, but the idea is that we want to do this on a monthly and on a fiscal year level. So we need to load all the data and depending on the filter that the user selected (Fiscal Year) or January, February, March we would need to get the latest survey and count of promoters.