Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
cander19
Contributor II
Contributor II

Cumulative Spend Expression

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.

Labels (1)
5 Replies
G3S
Creator III
Creator III

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
Contributor II
Contributor II
Author

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
Creator III
Creator III

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
Contributor II
Contributor II
Author

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
Creator III
Creator III

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