WTD with set analysis

[#1]

Hi All.

Is there someone that have any solution to calculate WTD (week to date) with a set analysis expression? My datefields format is YYYYMMDD.

Thanks! 

[jjfabian]

Hi there,

assuming that you have a calendar with a year and a week field available in data model, the following Set Analysis should give you a week to date:

$<Year= {$(=max(Year))}, Week= {"<=$(=max({<Year={$(=max(Year))}>} Week))"}>}

If you do not have Year and Week as fields in your data model, simply add them in your script:

year(<yourdatefieldhere>) as Year

week(<yourdatefieldhere>) as Week

Regards,

Jakob

[#3]

Hello,

Thanks for reply. Yes I have a calender in my data model but I don't really get it to work properly. My set analysis looks like:

sum({$<Year={"$(=max(Year))"}, Week={"$(=max({<Year={$(=max(Year))}>} Week))"}>} Sales)

Do you have any suggestions?

[jjfabian]

Well, set analysis an dates can give you trouble. Dates have very different formats depending on what region code you use and so on. Your data in your calendar may look entirely different.

I would suggest that you "test" your set analysis somewhere else first: Use a text object here and look for what the individual parts [=max(Year) and =max(Week)] of the set analysis give when you have a year an a week selected.

In most cases this is where you can find the error in your set analysis.

[#5]

Ok, I understand. The date format I use looks like 'YYYYMMDD'. Could I use the date function in the set analysis, if so where?

It works fine when I test my individual parts max(Year) and max(Week).