YTD transformation 2

[bittmapp]

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.

Thanks,

bitt

 

[nlim]

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.

 

[dinzana]

Bittmapp,

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(&quot;year(#0),? DT) = applysimple(&quot;year(#0),[date attribute]@ID)) AND (applysimple(&quot;#0&quot;,[date attribute]@ID)<= applysimple(&quot;#0&quot;,? 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.

-dinzana

 

[bittmapp]

Thanks Dinzana,

would you show me the additional applysimple functions you mentioned?

bitt

 

[dinzana]

Something like this for QTD:

(ApplySimple(&quot;to_char((#0),'YYYYQ')&quot;, ?DT) = ApplySimple(&quot;to_char((#0),'YYYYQ')&quot;, [date attribute]@ID)) AND (ApplySimple(&quot;#0&quot;, [date attribute]@ID) <= ApplySimple(&quot;#0&quot;, ?DT))


And for MTD:

(ApplySimple(&quot;to_char((#0),'YYYYMM')&quot;, ?DT) = ApplySimple(&quot;to_char((#0),'YYYYMM')&quot;, [date attribute]@ID)) AND (ApplySimple(&quot;#0&quot;, [date attribute]@ID) <= ApplySimple(&quot;#0&quot;, ?DT))


Hope that these help,

dinzana