|
Jayanthan Sivaloganathan wrote:
Hi All
Could you please explain the meaning of Set Analysis given below
sum({$<YEAR = {$(=max(YEAR))},MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH))"}>} Sales )
Further how can i change this to Yeartodate Calculation, further is there any pdf to learn about set analysis
i would like to learn to calculate YTD current year as well as previous year
Ishfaque Ahmed
Evan Kurowski
Jagan Mohan
Best Regards
Jayanthan
|
Hello Jayanthan,
It looks like you are performing a nested set-analysis expression summing the field [Sales] using a two field filter, [YEAR] and [MONTH].
- You are grabbing the maximum YEAR out of all possible years.
- You have nested expression which grabs all months less than or equal to the maximum MONTH that is found in the maximum selected YEAR.
The granularity possible here is at the monthly level (meaning if you wanted to be able to jump to any calendar date and know what the YTD value for that given date was, you could not "split" a month using this method. If the maximum date selected were the 15th of the current month and you wanted to compare against 1 month prior, you would only be able to compare 15 days from the current month against 28-31 days from the prior month).
Oftentimes I get requests for set-analysis comparison to calculate matching time frames. If I am at the 6th day of the current year, the request is to compare against only the first 6 days of the prior year (and not the entire month of the prior year), because comparing 6 days from January of this year against the full 31-days from the January of the prior year will not yield a true apples-to-apples comparison until the current month is fully complete and populated with 31 days of data. So the question often is.. "where do we stand against how we were doing on this day of the year, last year".
To have the best granular control over your chronological set-analysis, form a date field that tags your fact information, and convert this date field to a pure integer field. If the field only deals with Monthly level reporting, you can force each integer to either the first day or last day of each month via MonthStart() or MonthEnd() functions.
Once you have a DATE_NUM field, all set analysis is then applied to an integer field and no other calendar fields need to be involved to pluck any range you like from the entire data span:
DATENUM={">=$(Start) <=$(End)" }
Plugging in a variety of start or end points doesn't change the syntax of the base expression. Key points in timeline are always the same and will float based on user-selection:
Current Selected Date = Max(DATENUM)
Current Year Start = Num(YearStart(Max(DATENUM)))
Prior Year Selected Date = Num(AddYears(Max(DATENUM)-1))
Prior Year Start = Num(YearStart(AddYears(Max(DATENUM),-1)))
Take for example a 60-day rolling calculation... it gets complicated when everything is packaged in full months only, and the year-turnover points start making plucking the right combinations of YEAR/MONTHs from the beginning of this year and the end of last year require additional logic, formulas, etc... If you keep all set-analysis expressions centered around a DATENUM, they follow the same principle:
DATENUM={"> MAX(DATENUM)-60 <= MAX(DATENUM)"}
No matter where you reside in your result set, you should be able to get YTD from the selection point, and prior YTD of the same timeframe, down to the day level
If at any point your application starts caring about time of day and timestamps, you're already set-up to do this without having to change set-analysis structure. Allow the DATENUM to switch from an integer to a float by introducing timestamp fractions into the data, no need to add new set-analysis filtering fields.
