How to display a value based on the maximum date for same Issue

[varunreddy]

Hi Guys,

I am trying to pick the maximum status for an issue based on the maximum Date available. Please find the attachment below.

I have 3 fields Date, Status and ID and below is my dataset.

Date          Status                 ID

Mar-17      In Progress        123

Apr-17      In Progress        123

Mar-17     Opened              245

Apr-17      In Progress        245

I want to see this as my output:

Date          Status                 ID

Apr-17      Resolved            123

Apr-17      In Progress        245

For an ID, I need the status for max Date of an ID.

I have attached data source and QVW I created. I am getting status for min date and I am not sure, Where I am doing it wrong.

Can anyone please take a look at this?

Thanks in advance!

Regards,

Varun

[varunreddy]

Hi All,

Thanks for your quick responses. I am looking for the front end solution. I also have different conditions in my expression.

Can we set the below expression for the max date?

count({<[Pull Type]= {'M'},[Open Age] = {'> 0 & <= 30 Days'},[DQ Item Type] = {'DQ Issues'},Severity = {'Critical','High'},
Resolution Due Date Month Year] -= {'UNASSIGNED'},
Status -= {'Draft','Cancelled'}>}distinct Id)

Date field Calculation:

=aggr(max({<[Open Age] = {'> 0 & <= 30 Days'},[Pull Type] = {'M'} >}[As of Date]),Id,Status)

[Anil_Babu_Samineni]

Even though, Why can't you add direct like below. If your expression returns some value then below should work for you

count({<[Pull Type]= {'M'},[Open Age] = {'> 0 & <= 30 Days'},[DQ Item Type] = {'DQ Issues'},Severity = {'Critical','High'},
[Resolution Due Date Month Year] -= {'UNASSIGNED'}, Status -= {'Draft','Cancelled'}, Date = {'$(=Max(Date))'}>}distinct Id)

[varunreddy]

Hi Sunny,

I am looking for front end solution.

I am using calculated dimension for date.

=aggr(max({<[Open Age] = {'> 0 & <= 30 Days'},[Pull Type] = {'M'},[As of Date] = {'>=$(=vYearStart) <= $(=vMaxMonthDate)'}>}[As of Date]),Id,Status)

Here I am picking max date for an issue that is between o to 30 days

This is my expression:

count({<[Pull Type]= {'M'},[Open Age] = {'> 0 & <= 30 Days'},[DQ Item Type] = {'DQ Issues'},Severity = {'Critical','High'},
Resolution Due Date Month Year] -= {'UNASSIGNED'},
Status -= {'Draft','Cancelled'}>}distinct Id)

Here I am counting distinct issues for the above filters. After doing this, I found the issue mentioned above.

[sunny_talwar]

Do you have a single max date across all IDs or do you have different max dates? Would you be able to share a database which is closer to your actual problem to help you better here?

[effinty2112 · Accepted solution]

Hi varun,

Maybe

Date(Max(Date),'MMM-YY')	FirstSortedValue(Status,-Date)	ID
Apr-17	Resolved	123
Apr-17	In Progress	245

[varunreddy]

Hi Anil,

This should be my condition to display max date.

=aggr(max({<[Open Age] = {'> 0 & <= 30 Days'},[Pull Type] = {'M'} >}[As of Date]),Id,Status)

[varunreddy]

Hi Andrew,

Thanks for your response. This worked!

Cheers,

Varun

[varunreddy]

Hi All,

I got this. Thanks for your quick responses. I have learnt new things todays.

Thanks again for your help!

Cheers,

Varun

[Anil_Babu_Samineni]

Even, You have written set analysis. Why do you need dimension as expression. Because, It kills the performance while we are using Calc. Condition and although i would ask you to share sample application where you are doing this?