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

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

set analysis not possible ? implicit field value definition - date less than dimension

Hello,

Thanks for reading my (first !) message. Here's my question:

I have a table of Work orders, identified by WONUM (work order number/ID), which have a REPORTDATE (date created) and a FINISHDATE (date closed).

For each WONUM, I want to return the maximum FINISHDATE for the set of WONUM which have REPORTDATE before that of this WONUM.

For instance with the table below, I have given the wanted results in the last column: I now need to code this last column.

WONUMREPORTDATEFINISHDATEMax finishdate of WONUM reported before
BC1425292011/08/012011/08/042011/08/04
BC1427962011/08/022011/08/022011/08/04
BC1428052011/08/032011/08/112011/08/11
BC1428082011/08/042011/08/052011/08/11
BC1435232011/08/052011/08/122011/08/12

I have tried with Set analysis to return a subset of WONUM which have a REPORTDATE before that of the current WO, with no sucess (no aggregation). Also I cannot/dont want to use getfieldselection functions and such as this is independent of the selection(s).

I got so far (I'm not sure this is the best way to approach this):

max(

{$<WONUM =

P({1<REPORTDATE={"<=$(= ///// here REPORTDATE OF THIS WONUM as dynamic value ////// ))"}>} )

>} 

total FINISHDATE)

>> Return max of FINISHDATE from selection where (WONUM which have a REPORTDATE before that of this WONUM)

But no luck so far, I hope you have some ideas !

Thanks


Arthur

1 Solution

Accepted Solutions
Not applicable
Author

I found the solution.

I loaded the REPORTDATE and FINISHDATE twice, with no link between the two.

Then compare the dates to return the max FINISHDATE for REPORDATE before.

View solution in original post

2 Replies
Not applicable
Author

Hi

I have attached a .qvw. I'd really appreciate your help !

Thanks


Arthur

Not applicable
Author

I found the solution.

I loaded the REPORTDATE and FINISHDATE twice, with no link between the two.

Then compare the dates to return the max FINISHDATE for REPORDATE before.