Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
LorenaGSaar
Contributor II
Contributor II

Aggr Dates

Hello All,

I'm struggling with Aggr, and today I have a list of datasets by client.

LorenaGSaar_0-1643902021881.png

I have the data received, and data delivered.

I want to do a difference between data received and delivered, so I need to take the min data received.

I'm trying with aggr function but with no success.

 

Aggr(Min([Data received on]), Client, [Output desc],[GDM Dataset ID], [Data received on], [Delivered on])

 

Could you please let me know what am I doing wrong?

thank you!

Labels (1)
1 Solution

Accepted Solutions
jbhappysocks
Creator II
Creator II

Hi. As chrismarlow says you don't really need an aggr for this,  

min(total <App> [Data Received on]) will do it

 

 

View solution in original post

5 Replies
chrismarlow
Specialist II
Specialist II

Hi,

I am not sure why you want to use AGGR for that, rather than just take the one date from the other. Do you mean minimum date ignoring some of your dimensions? That would need a TOTAL qualifier.

Maybe try a putting up a couple of worked examples with the answer you would like to see?

Cheers,

Chris.

LorenaGSaar
Contributor II
Contributor II
Author

Hello Chris, thank you for your reply.

Another example:

LorenaGSaar_1-1643965897030.png

 

So I have 3 apps: ABC, CDE, EFG.

App ABC has 2 datasets IDs, the 1 received on 24/06/2021 and the other one on 25/06/2021. Once the dataset is completely received for app ABC, it can be delivered to client, which happened on day 26/06/2021. So I need to get the min date received, because I want to do a diff between the min date received dataset - the date delivered. So in this case, the min date is 24/06, the app was delivered on 26/06 so my diff is 2 days.

So that is why I though about doing an aggr, to get the min(Data received on). 

In SQL I would do like this:

rank() over (partition by App order by date received on asc) as rank_dates;

and after ranking my min date, I would filter where rank_dates = 1;

Hope it's more clear 🙂

jbhappysocks
Creator II
Creator II

Hi. As chrismarlow says you don't really need an aggr for this,  

min(total <App> [Data Received on]) will do it

 

 

Levente_Szittya
Partner - Contributor III
Partner - Contributor III

Plus 1 vote for min(total <App> [Data Received on]) 

LorenaGSaar
Contributor II
Contributor II
Author

Perfect, that was exactly what I need. Thank you all!