Calculating the Date Difference Between Non Aggregate Date & Aggregate Date

[Biswajit_1993]

Hi Team,
I am not able to calculate the date difference between the non aggregate date and aggregate date any idea how can I do it?

E.G - dateDifference(Created Date, Max(End Date), ''DD")

Thanks & Regards
Biswajit Dash

[Koushik_Muthanna]

Hi @Biswajit_1993 ,

Create a calculated field using LAC-W

maxtime: maxOver({End Date}, [ ] ,PRE_FILTER)

Create another calculated field to get the date difference

datediff: dateDiff({Created Date},maxtime,‘DD’)

Regards,
Koushik

[Biswajit_1993]

Thanks @Koushik_Muthanna for your help. This calculation is perfectly work for me but one more thing I need your help like for a particular work item there are multiple end date so the calculation is taking right as Max End Date but what happened for each created date it subtract the max end date.

PFB the example

If you see the below example for a work item it is showing multiple Date Duration so what happened when I am trying to summation of all the Date Duration as Days then duplicate data also summing so I need only one Duration for each Work Item.

Thanks & Regards
Biswajit Dash

[Koushik_Muthanna]

Hi @Biswajit_1993 ,

You have data at a day granularity and you are grouping it a higher granularity by work item.
Can you provide an example and show what is the expected value?

Kind regards,
Koushik

[Biswajit_1993]

HI @Koushik_Muthanna ,

From this three highlighted color box I need only the one one number so summation should be = 2795+2082+1759 Total( 6636)

PFB the picture

If you see the above picture the yellow highlighted rows are the Date Duration and Blue Highlighted are the Summation between the Date Duration .

So in the Blue highlighted rows it is showing duplicate number of Summation .
For the second rows instead of 2794 it is showing 5590 and 3rd rows instead of 2081 it is showing 14574 and so on .

Thanks & Regards
Biswajit Dash

[Koushik_Muthanna]

Hi @Biswajit_1993 ,

You would require a combination of LAC-W and LAC-A

Here is an example :
1/ The idea is to first to generate row level calculations using LAC-W
2/ Then use that in an LAC-A to get values at required level of grouping

max_ship_date [ LAC-W ]

maxOver({Ship Date},[{Product Category},{Order_Date_Timestamp}],PRE_FILTER)

date duration in days [ Date Difference at a day level ]

dateDiff({Order_Date_Timestamp},{max_ship_date},‘DD’)

duration_days_lac [ LAC-A ]

min({date duration in days},[{Product Category},{Order_Date_Timestamp},{max_ship_date}])

Regards,
Koushik