Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
varunreddy
Creator III
Creator III

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

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

18 Replies
varunreddy
Creator III
Creator III
Author

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)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
varunreddy
Creator III
Creator III
Author

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

Hi varun,

Maybe

Date(Max(Date),'MMM-YY') FirstSortedValue(Status,-Date) ID
Apr-17Resolved123
Apr-17In Progress245
varunreddy
Creator III
Creator III
Author

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
Creator III
Creator III
Author

Hi Andrew,

Thanks for your response. This worked!

Cheers,

Varun

varunreddy
Creator III
Creator III
Author

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?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful