topic Re: How to display a value based on the maximum date for same Issue in QlikView

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

varunreddy — Wed, 19 Jul 2017 13:56:04 GMT

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

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

sunny_talwar — Wed, 19 Jul 2017 14:03:59 GMT

Try this

Table:

LOAD Date,

Status,

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

Right Join (Table)

LOAD ID,

Max(Date) as Date

Resident Table

Group By ID;

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

Anil_Babu_Samineni — Wed, 19 Jul 2017 14:04:32 GMT

May be this?

Dimension is MonthName(Date) and second dimension

Expression - Only({<Date = {'$(=Max(Date))'}>} ID)

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

sunny_talwar — Wed, 19 Jul 2017 14:05:35 GMT

Other way and probably a slightly more efficient one

Table:

LOAD Date,

Status,

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

NoConcatenate

LOAD *

Resident Table

Where ID <> Previous (ID)

Order By ID, Date desc;

DROP Table Table;

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

antoniotiman — Wed, 19 Jul 2017 14:13:59 GMT

Hi Varun,

Maybe this

Regards,

Antonio

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

Kushal_Chawda — Wed, 19 Jul 2017 14:16:21 GMT

Data:

LOAD date(Date, 'MMM-YYYY') as Date,

Status,

FROM

[Book1 (2).xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join(Data)

LOAD date(max(Date),'MMM YYYY') as Date,

ID,

1 as Flag

Resident Data

Group by ID

Create straight table

Dimension:

Expression

1) Date

=Only({<Flag={1}>}Date)

2) Status

=Only({<Flag={1}>}Status)

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

sunny_talwar — Wed, 19 Jul 2017 14:18:14 GMT

Did I miss something, all of you are giving front end solutions... did the OP specifically asked for front end solution? I am sorry if I missed it

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

sunny_talwar — Wed, 19 Jul 2017 14:18:57 GMT

Just of curiosity

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

Kushal_Chawda — Wed, 19 Jul 2017 14:20:48 GMT

Mine is mixed, front and back end

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

varunreddy — Wed, 19 Jul 2017 14:21:25 GMT

Hi Anil,

I am having different filters in the expression I am using. Can we accommodate in your 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)

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

varunreddy — Wed, 19 Jul 2017 14:23:54 GMT

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)

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

Anil_Babu_Samineni — Wed, 19 Jul 2017 14:29:32 GMT

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

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

varunreddy — Wed, 19 Jul 2017 14:29:51 GMT

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:

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

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

sunny_talwar — Wed, 19 Jul 2017 14:31:34 GMT

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?

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

effinty2112 — Wed, 19 Jul 2017 14:32:48 GMT

Hi varun,

Maybe

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

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

varunreddy — Wed, 19 Jul 2017 14:43:25 GMT

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)

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

varunreddy — Wed, 19 Jul 2017 14:51:54 GMT

Hi Andrew,

Thanks for your response. This worked!

Cheers,

Varun

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

varunreddy — Wed, 19 Jul 2017 14:52:43 GMT

Hi All,

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

Thanks again for your help!

Cheers,

Varun

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

Anil_Babu_Samineni — Wed, 19 Jul 2017 15:04:13 GMT

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?