Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Rest API Dynamic Date Query

Hi Everyone. I am having a problem with the Ariba API dynamic time filter. Code is listed below.   Source = Json.Document(Web.Contents("https://openapi.ariba.com/api/analytics-reporting-details...
Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi Anonymous 

     

    You can adjust your code to:

    let
    startMonthDate = Date.StartOfMonth( Date.From( DateTime.LocalNow() ) ) ,
    endDate = Date.AddDays(  startMonthDate , -1 ),
    startDate = Date.AddMonths( startMonthDate , -2 ),
    endDateText = Date.ToText( endDate, "yyyy-MM-ddT00:00:00Z" ),
    startDateText = Date.ToText( startDate, "yyyy-MM-ddT00:00:00Z" ),
    Custom1 = "https://openapi.ariba.com/api/analytics-reporting-details/v1/prod/views/templatename?realm=myrealm&filters={""createdDateFrom"":"& startDateText &",""createdDateTo"":"&endDateText&"}"
in
    Custom1

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
6 years ago

Hi Anonymous 

 

Try something like this.

Edited 2.0:

 

 

let
    endDate = Date.From( DateTime.LocalNow() ),
    startDate = Date.AddMonths( endDate , -2 ),
    endDateText = Date.ToText( endDate, "yyyy-MM-ddT00:00:00Z" ),
    startDateText = Date.ToText( startDate, "yyyy-MM-ddT00:00:00Z" ),
    Custom1 = "https://openapi.ariba.com/api/analytics-reporting-details/v1/prod/views/templatename?realm=myrealm&filters={""createdDateFrom"":"& startDateText &",""createdDateTo"":"&endDateText&"}"
in
    Custom1

 

 

 

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Mariusz .

     

    Thank you. Really appreciated. The Code is working for me.

    Just a last Question. What if i want to skip the current month and filter for last two months. 

    For Example: i want to skip the current march month 3 days etc, and filter the last whole february and january Months.

     

    Many Thanks.

     

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

     

    You can adjust your code to:

    let
    startMonthDate = Date.StartOfMonth( Date.From( DateTime.LocalNow() ) ) ,
    endDate = Date.AddDays(  startMonthDate , -1 ),
    startDate = Date.AddMonths( startMonthDate , -2 ),
    endDateText = Date.ToText( endDate, "yyyy-MM-ddT00:00:00Z" ),
    startDateText = Date.ToText( startDate, "yyyy-MM-ddT00:00:00Z" ),
    Custom1 = "https://openapi.ariba.com/api/analytics-reporting-details/v1/prod/views/templatename?realm=myrealm&filters={""createdDateFrom"":"& startDateText &",""createdDateTo"":"&endDateText&"}"
in
    Custom1

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn