Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Sameer9585
Creator II
Creator II

Open and Close Tickets

Hi,

I need a help to get the ID till the selected date and the there should be an another field of flag which indicates close or open it is been decided by the End_Date if the selected date is less than or equals to End_Date the it should display all the ID till that date and the status Open/Close.

Below I provide the dataset in it sheet 11 is the dataset.

thanks in advance,

With Regard,

1 Solution

Accepted Solutions
asinha1991
Creator III
Creator III

create a date island(a date field which is not associated with the tables) using autogenerate etc.

Add that date field in list box, lets say fieldname is dateval. 

now, create a straight chart. the first dimension will be(supress null),

if(START_DATE<=getfieldselctions(dateval) ,ID)

now create this expression and name it status

if(END_DATE<=getfieldselctions(dateval) ,'Closed','Open')

 

if you want to use any associated date field as filter then you will have to use aggr only in place of direct if with 1 

for example

dimension will be

aggr(only({1}if(START_DATE<=getfieldselctions(START_DATE) ,ID)),ID)

 

View solution in original post

14 Replies
asinha1991
Creator III
Creator III

create a date island(a date field which is not associated with the tables) using autogenerate etc.

Add that date field in list box, lets say fieldname is dateval. 

now, create a straight chart. the first dimension will be(supress null),

if(START_DATE<=getfieldselctions(dateval) ,ID)

now create this expression and name it status

if(END_DATE<=getfieldselctions(dateval) ,'Closed','Open')

 

if you want to use any associated date field as filter then you will have to use aggr only in place of direct if with 1 

for example

dimension will be

aggr(only({1}if(START_DATE<=getfieldselctions(START_DATE) ,ID)),ID)

 

Sameer9585
Creator II
Creator II
Author

Hi,

I have query if I select a date from filter then It is not displaying ID till that date it is only displaying the particular date.I need ID till that date

formosasol
Partner - Contributor III
Partner - Contributor III

Hi Sameer,

You can also do the following

Create a Table in Qlik Sense and add the following Colums as Dimensions (ID, Start_Date, End_Date) and Status

the formula for status should be "=if(isnull(End_Date),'Open','Close')"

This will show you all the open and closed information where the End_Date is not populated or is null

Open Close 1.jpg

Formula

Open Close 2.jpg

 

Regards

Frank

asinha1991
Creator III
Creator III

is the date field in filter one of start_date or end end_date? then you have to use '1' as set identifier(with aggr).

it will be easier to create another calendar in script which is not related and put that in filter
Sameer9585
Creator II
Creator II
Author

No, I'm saying that if I select the date from date filter then it should display all the ID present till that date.How to do it?
asinha1991
Creator III
Creator III

I understand your issue, the problem is that  when you select a date from filter whole set is assigned that date.

Even though we have greater than equal to condition, since your data is directly related to that date it selects only one date. This issue will not happen if you a separate calendar in the script

that's why I am asking what is the field used in filter and then I will suggest exact solution

Sameer9585
Creator II
Creator II
Author

Date created from the master calender
asinha1991
Creator III
Creator III

there must be a common key field between your master calendar and your table.

Remove the common key field or create another master calendar without any linkage, use that field in filter. Modify fieldname in getfieldselections accordingly

Sameer9585
Creator II
Creator II
Author

I have tried still not working