topic Re: Dynamic Set Analysis in App Development

Dynamic Set Analysis

nicouek — Wed, 26 Jun 2024 19:39:45 GMT

Hello everyone,

Count({$<Date={'09/30/2019','09/30/2020','09/30/2021','09/30/2022','09/30/2023',"$(=Date(Date#(Today(),'M/DD/YYYY'),'MM/DD/YYYY'))"}>}distinct Sales)

I wrote this set analysis to resolve [show sale for past 5 years + running current year]

Is it possible to make past years dynamic as well so that on 30 Sept 2024 09/30/2023 -> becomes 09/30/2024 and other previous year respectively? Or should I put current year on another sheet?

FYI: My DIMENSION IS A "YEAR" FIELD.

If you have an idea please help.

Thank you

Re: Dynamic Set Analysis

marksouzacosta — Wed, 26 Jun 2024 21:38:45 GMT

Hi @nicouek,

You can try the code below, it is not pretty, but works. Don't forget to replace your field names properly:

Sum({<O_ORDERDATE = {"$(=AddYears(Today(),-5))", "$(=AddYears(Today(),-4))", "$(=AddYears(Today(),-3))", "$(=AddYears(Today(),-2))", "$(=AddYears(Today(),-1))", "$(=Today())"}>} O_TOTALPRICE)

Regards,

Mark Costa

Re: Dynamic Set Analysis

marcus_sommer — Thu, 27 Jun 2024 11:28:03 GMT

I think I would use a shortened date without the year-information - just: DD/MM created within the calendar - and then the user could select the wanted one as well as the wanted years.

If not an extra date-field is wanted within the UI else the full-date should be used for the selection - the short-date might be referenced, like:

count({< ShortDate = p(ShortDate), Date >} Value)

Re: Dynamic Set Analysis

nicouek — Thu, 27 Jun 2024 13:16:16 GMT

Hi @marksouzacosta,

I think I am on the right path with your suggestion, I would try a few other approaches and follow back with you; if you have another idea please share.

Thank you

Re: Dynamic Set Analysis

ali_hijazi — Fri, 28 Jun 2024 06:39:27 GMT

Wondering why you are using sum(distinct Sales); this means if you have same amounts on different rows like for example 500, 500 then you will sum up only one 500

anyways you expression would be something like this:
sum(

{

<order_date={">=$(=AddYears(Today(),-5))"}>

<order_date ={"=$(=Today())"}>

} Sales)

Re: Dynamic Set Analysis

nicouek — Wed, 17 Jul 2024 16:50:19 GMT

Hi, @marksouzacosta

I have been taking a stab at this problem as often as I could, and the closest syntax that worked for me is:

Count({$<Order_Date={"09/30/20**"}>} distinct Order_ID) -> Courtesy of "Singa Christodoulou"

Keeping my counts of orderIDs on the desired DATE of the year and letting my [YearDimension] display the years. Adding a running current year set modifier did not do so well... Thanks a lot will keep working on the Running Current Year metric

Thank you