YTD calculations

[sspk]

Hi All,
I have a query subject (GL-Actual) which has data items like year,dept_name,account#,description,Jan, feb, mar,..Dec(Month wise totals ).I need to calculate current year YTD , prior year YTD.using these month wise  query items.(Table has 2 years of data)
year
dept_name
account#
jan
feb
:
Dec
Any suggestions will help me a lot

[blom0344]

That looks like a denormalized (flattened) reporting table where facts are already stored in their respective monthly buckets. You cannot use SQL logic directly, since the YTD through the year would require you to fetch more or less columns.

A way around this is to use a logical structure on top of the reporting table:

Code Select Expand


CASE
WHEN
extract(month,[current_date]) = 1 and year = extract(year,[current_date])
THEN 0
WHEN
extract(month,[current_date]) = 2 and year = extract(year,[current_date])
THEN [jan]
WHEN
extract(month,[current_date]) = 3 and year = extract(year,[current_date])
THEN [jan]+[feb]
....................
....................
WHEN
extract(month,[current_date]) = 12 and year = extract(year,[current_date])
THEN [jan]+[feb]+[mar]+[apr]+[may]+[jun]+[jul]+[aug]+[sep]+[oct]+[nov]
ELSE 0 END


For the previous year:

Code Select Expand


CASE
WHEN
extract(month,[current_date]) = 1 and year = extract(year,[current_date])-1
THEN 0
WHEN
extract(month,[current_date]) = 2 and year = extract(year,[current_date])-1
THEN [jan]
WHEN
extract(month,[current_date]) = 3 and year = extract(year,[current_date])-1
THEN [jan]+[feb]
....................
....................
WHEN
extract(month,[current_date]) = 12 and year = extract(year,[current_date])-1
THEN [jan]+[feb]+[mar]+[apr]+[may]+[jun]+[jul]+[aug]+[sep]+[oct]+[nov]
ELSE 0 END


This may need some tweaking, depending on your exact needs and the report is obviously dynamic, rerunning in another month will geve new results.
Also check on the values in the monthly buckets. If null values are used instead of 0 , then you need a coalesce to be able to make the addition work..