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]

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.

[#3]

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.

[swuehl]

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.

[#5]

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.