Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.
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
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.
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.
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.
Got your requirement . I still have few questions:
Regards,
Aditya
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.
Something like this ?
Regards,
Aditya