Bar Chart of Yearly Total Costs (given a table of cost schedules)

[Anonymous]

Hi Experts,

Been going at this for days. Very tricky as I can't seem to figure out both the dimension and the expression. Not even sure if possible (I'd like to think that it is). I have a simple table of expense schedules loaded as follows:

Cost Type	Start Date	End Date	Monthly Cost
EXPENSETYPE 1	March 1, 2010	December 31, 2020	100
EXPENSETYPE 1	February 1, 2016	December 31, 2019	80
EXPENSETYPE 2	January 1, 2016	April 30, 2019	350

I have a variable for a Report Date. The chart should calculate the total yearly costs beginning from this "Report Date".

So if Report Date for example is 10/1/2015 then for the chart for EXPENSE TYPE 1, the Dimensions should be 2015, 2016, 2017, 2018, 2019, 2020.

Essentially the bar chart should display the values as they are below:

Year	Total Cost
2015	(100*3) = 300
2016	(100*12) + (80*11) = 2080
2017	(100*12) + (80*12) = 2160
2018	(100*12) + (80*12) = 2160
2019	(100*12) + (80*12) = 2160
2020	(100*12) = 1200

Hoping for any assistance. Attaching working document with the loaded table. You can ignore most of my initial attempts.

Thank you!

[simenkg · Accepted solution]

// We create a calendar and match the months to the intervals.

// The expression then becomes sum({<DateNum= {">=$(vReportDate)"}>} [Montly Cost])

Directory;

Costs:

LOAD [Cost Type],

//    [Start Date],

//    [End Date],

    [Monthly Cost],

    Date#([Start Date],'MMM-DD-YYYY') as [Start Date],

    Date#([End Date],'MMM-DD-YYYY') as [End Date]

FROM

Costs.xlsx

(ooxml, embedded labels, table is Sheet1);

minMax:

load min([Start Date]) as minDate,

  max([End Date]) as maxDate

  resident Costs;

let vMinDate = Floor(Peek('minDate',0,'minMax'));

let vMaxDate = Floor(Peek('maxDate',0,'minMax'));

tempDates:

load $(vMinDate) + RowNo() - 1 as tempDate

AutoGenerate $(vMaxDate)-$(vMinDate)+1;

Calendar:

load Distinct MonthStart(tempDate) as %DateKey,

  Floor(MonthStart(tempDate)) as DateNum,

  Year(tempDate)&'-'&Month(tempDate) as YearMonth,

  Year(tempDate) as Year

  resident tempDates;

Left join (Costs)

IntervalMatch(%DateKey)

load [Start Date], [End Date] resident Costs;

drop table minMax;

drop table tempDates;

//Hope this helps. SKG

[Anonymous]

I-N-C-R-E-D-I-B-L-E! Thank you!