0

Select json values by date

Hi,

I'm not working with JSON files a lot. How can i select the sales for 1 year, for example 2020. And not only static '2020', but also dynamic so i always have 'last years' (next year it will display 2021 as last year) sales by month?

2 comments

  • Avatar
    Parker Selman Official comment

    Hi Tom! 

    Thanks for the post. In order to get this working, we'll need to take advantage of the SELECT and DATE_IN functions, as well as the kf:names XPATH function to select only the entries that occur in the previous year. 

    The formula below should return the sales figures for last year, though you could swap 'sales' for any of the entries listed under each totals node:

    SELECT( @/totals/*/sales , DATE_IN ( DATE ( @kf:names(totals) , "yyyy-MM" ), year , -1 )

    This formula returns only the sales entry that are in the previous year. Because we're using a relative year reference in DATE_IN , this formula will return 2020 this year, 2021 next year, and so on. 

    Hope this helps! If you have any questions around this, let us know by submitting a support ticket with our team! 

    Best, 

    Parker

  • 0
    Avatar
    Tom Boersma

    That's it. Thanks!

Please sign in to leave a comment.