Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)
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.
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?
Hi varun,
Maybe
Date(Max(Date),'MMM-YY') | FirstSortedValue(Status,-Date) | ID |
---|---|---|
Apr-17 | Resolved | 123 |
Apr-17 | In Progress | 245 |
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)
Hi Andrew,
Thanks for your response. This worked!
Cheers,
Varun
Hi All,
I got this. Thanks for your quick responses. I have learnt new things todays.
Thanks again for your help!
Cheers,
Varun
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?