Please explain to me how, if possible, to create a Year to Date transformation without having a 'transformation table' that stores the data in the warehouse.
Just a point of clarification, the year-to-date transformation table does not store the data that is being calculated. It just stores the mappings between a date, and the dates that occurred before it. So a YTD transformation table will look like this.
date ytddate
---- -------
jan 01 jan 01
jan 02 jan 01
jan 02 jan 02
jan 03 jan 01
jan 03 jan 02
jan 03 jan 03
i don't know of an expression that can solve your issue. but the ytd transformation is good because it can be used for ANY metric at ANY level.
You can create a simple date prompt [DT] and then create a filter [F1] with an 'advanced qualification' and 'custom expression' with an applysimple expression in it....something like this
(applysimple("year(#0),? DT) = applysimple("year(#0),[date attribute]@ID)) AND (applysimple("#0",[date attribute]@ID)<= applysimple("#0",? DT))
In a 'Nutshell' what this says is:
Give me the data that has the same year as the value entered into the prompt but make sure that the date is also less than or equal to the value entered into the prompt.
With a few additional applysimple statements this can also be used to create QTD and MTD. Let me know if you need additional information. I hope this helps.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.