Aggr Dates

[LorenaGSaar]

Hello All,

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

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!

[chrismarlow]

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]

Hello Chris, thank you for your reply.

Another example:

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 · Accepted solution]

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

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

[Levente_Szittya]

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

[LorenaGSaar]

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