Announcements
cancel
Showing results for
Did you mean:
Creator

## Dynamic Set Analysis

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.

Thank you

Labels (2)

• ### General Question

5 Replies
Partner - Specialist

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

Read more at Data Voyagers - datavoyagers.net

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)

Creator
Author

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

Partner - Master II

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 ={"=\$(=Today())"}>

} Sales)

I can walk on water when it freezes
Creator
Author

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

Tags