Cumulative Spend Expression

[cander19]

Getting my feet wet with a dashboard and looking for YTD expenses for Material cost category. I have a table of data - example below:

Cost Category	Month	Monthly Expense
Total Personnel	January	$                      2,000
Total Personnel	February	$                      2,000
Total Personnel	March	$                      2,100
Total Personnel	April	$                      2,000
Total Personnel	May	$                      2,100
Total Personnel	June	$                      2,100
Total Personnel	July	$                      2,200
Total Personnel	August	$                      2,200
Total Personnel	September	$                      2,200
Total Personnel	October	$                      2,200
Total Personnel	November	$                      2,220
Total Personnel	December	$                      2,200

Total Material	January	$                             700
Total Material	February	$                             700
Total Material	March	$                             350
Total Material	April	$                             350
Total Material	May	$                             400
Total Material	June	$                             400
Total Material	July	$                             400
Total Material	August	$                             400
Total Material	September	$                             350
Total Material	October	$                             375
Total Material	November	$                             375
Total Material	December	$                             350

Jan-Jun data is actual and Jul-Dec data is forecasted. I am looking for YTD cumulative expense. For a bar chart, the following expression works great:

rangesum(above(Sum({<[Cost Category]={'Total Material'}>} num(trim([Monthly Expense]))),0,rowno()))

This shows the cumulative total by month.  I am trying to use something similar to show how much I spent on Material through June (or whatever month I decide).  Any help would be appreciated.

[G3S]

using GetFieldSelections in the chart could work. (selecting the required month through the filter pane )

https://help.qlik.com/en-US/sense/May2022/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/FieldFunct...

[cander19]

Yes, that does work - thank you. But what I'd like it to do is calculate the YTD automatically based on the date. So, if today is July 18th, it calculates Jan-Jun. If it is September, then it calculates Jan-Aug. I am using the gauge chart and was trying to get the amount spent as of the last full month.

[G3S]

then a something like this could work with set analysis for calculating the year as year(today()) and months as month(today())-1

rangesum({< [Cost Category]={'Total Material'}, [Year]={'$(=year(today()))'}, [Month]={'<=$(=month(today())-1)'}>}[Monthly Expense])

I'm still a beginner with set analysis, hope this works.

[cander19]

Thanks again - yes, set analysis is the way to go. This doesn't quite work  - I think it will work if I have another column that translates the month string names (January, Febraury, etc) into their corresponding numbers (1,2...12).  

[G3S]

num(month(yourDATE)) also would work to get 1-12. but yes, easier if there's a field already.