Dynamic Set Analysis

[nicouek]

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

[marksouzacosta]

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

[marcus_sommer]

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) 

[nicouek]

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

[ali_hijazi]

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)

[nicouek]

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