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

[Sara_3]

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.

[Aditya_Chitale]

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 ?

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]

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]

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]

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]

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]

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]

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]

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?

Thanks,

Sara.

[Aditya_Chitale]

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:

Regards,

Aditya