Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Sara_3
Creator
Creator

Compare values from the same field using filter and without filter.

Hi all,

Requirement :

I need 2 date fields from Load Date in script . 

1- Load Date with selected Document and ID ( 3 dates above) - Table 2

2- Load Date without Selection (all data set of Load Date) (all 6 Dates) -  Table 1

 

Test1:

load 

Document, ID, Load Date

from QVD;

Table1:

Document ID  Load Date 
123 1 12/3/2021
123 1 10/4/2021
123 1 9/28/2021
456 1 6/23/2020
456 3 11/6/2021
456 3 1/5/2020

 

for example; When I selected the Document 123 and ID 1 I got this data:

Table 2 :

Document ID  Load Date 
123 1 12/3/2021
123 1 10/4/2021
123 1 9/28/2021

 

I tried multiple things but did not worked , Please help me this.

Thanks in advance.

13 Replies
Aditya_Chitale
Specialist
Specialist

Do you want 2 tables to be displayed one with working filter selection and other with ignored filter selection ? Is this what you want to achieve ?

Aditya_Chitale_0-1697521394208.png

For this, Add Document field as measure in the table in which you want to ignore selections and use below expression :

=only({<Document=,ID=>}Document)

Add other fields as regular dimensions.

 

Regards,

Aditya

Sara_3
Creator
Creator
Author

Thanks Aditya , your expression is  working but Can I get 2 separate fields or coming fields from 2 tables like 1 with selection and other without selection as you mentioned above  because I need to compare data . with the expression it will be difficult to compare multiple things. 

Thanks.

Aditya_Chitale
Specialist
Specialist

I didn't quiet get what you want to achieve. The solution I provided needs the expression to be written only in one measure rest all fields can be added without expression. If possible can you explain by giving an example of desired output ? What type of comparison are you trying to make exactly ?

 

Regards,

Aditya

Sara_3
Creator
Creator
Author

Yes , I did your expression and it is working. my final Output is little complicated and  consist of 2 steps to get the Days Aged :

final output:

Document  ID  Days aged 
123 1 6

 

Step 1: create a check 

for example :  comparison between 2 date fields in desc order is the same previous data exist in selected date field for example: in my table 2 I have 3 dates and in all I have 6 dates.

check = (if the previous data exist ,1 , 0)

selected data dates:

12/3/2021         check is 0 because in unselected date list the previous date is 11/6/2021 

10/4/2021          check is 1 because in unselected date list has same previous data , last date is same 9/28/2021

9/28/2021           check is 0 because previous data not exist  

Unselected date :                                              

Load Date
12/3/2021
11/6/2021
10/4/2021
9/28/2021
6/23/2020

1/5/2020

    

Document ID  Load Date  Check
123 1 12/3/2021 0
123 1 10/4/2021 1
123 1 9/28/2021 0

 

Step 2: Get Days Aged

if the check = 1 then difference between previous date and date to get Days Aged.

if check 1 = Days Aged  = 6 ,  date 10/4/2021 - previous date (9/28/2021)

Document ID  Load Date  Check Days Aged 
123 1 12/3/2021 0 0
123 1 10/4/2021 1 6
123 1 9/28/2021 0 0

 

I hope my example is helpful to understand my output.

Thanks.

Sara_3
Creator
Creator
Author

My following script is working fine for calculate Days Aged of all selected dates but I need to add the check .

if([ Document] = previous([Document]) and [ID] = previous([ID]),
RangeSum(peek('DaysAged'),[Date] - Previous([Date])) , 0) AS DaysAged

I think it should be like this :

if([ Document] = previous([Document]) and [ID] = previous([ID]) and Check = 1,
RangeSum(peek('DaysAged'),[Date] - Previous([Date])) , 0) AS DaysAged 

Thats why I need check in script not in expression.

Sara_3
Creator
Creator
Author

Hi Aditya ,

I really appreciated, basically i need to compare both fields 's (selected and unselected) previous date is same or not.  I need 2 separate fields . is it possible to create both fields ?

 

Thanks,

Sara.

Aditya_Chitale
Specialist
Specialist

Got your requirement . I still have few questions:

  1. do you  want to compare previous date based on group of document field ? For eg: 123 is one group so, if there is a date like 8/22/2021 present but have different document number, would there be check '1' for 9/28/2021 ?
  2. Also as per my understanding, you are basically comparing previous data on basis of month of that date or is there any other factor that you are using to check if previous date exist ?

Regards,

Aditya

Sara_3
Creator
Creator
Author

Hi Aditya,

1- Based on document and ID fields because Document has multiple IDs. for 9/28/2021 = 0 because it has no previous date when selected Document 123 and ID 1. let say : when the Date order by DESC the Min date check = 0 

2- basically , the Load date is when the data load it is not specifically the month or week or day.

for example: 

Selected Load Dates are 12/3/2021 , 10/4/2021, 9/28/2021 in between 12/3/2021 and 10/4/2021 , the data is also load on 11/6/2021 in (unselected). for load date 12/3/2021 check = 0 . because unselected Load Date list shows Data load on 11/6/2021.

Sorry, I was wrong that compare both previous date. To fulfill the requirement , I think the logic should be  to check if( Load date (selected ) = previous (load date ) unselected) ,1,0). What do you think?

Sara_3_0-1697630465125.png

Thanks,

Sara.

Aditya_Chitale
Specialist
Specialist

Something like this ?

test:
load * Inline
[
Document,ID,LoadDate
123,1,10/04/2021
456,3,01/05/2020
456,1,06/23/2020
456,3,11/06/2021
123,1,12/03/2021
123,1,09/28/2021
 
];
 
NoConcatenate
 
final:
load *,
    if(Check=1,LoadDate-peek(LoadDate)) as DaysAged;
load
Document,
    ID,
    LoadDate,
    if(Document = peek(Document) and  Month(Addmonths(LoadDate,-1)) = Month(peek(LoadDate)),1, 0) as Check
Resident test order by Document asc, LoadDate asc;
 
drop table test;
 
 
Output:
Aditya_Chitale_0-1697799004246.png

 

 

Regards,

Aditya