Hi guys
I've been trying for close to a week to get sum year to date and I simply can't work out how, and I'm in tears of frustration despite asking on these forums twice already.
The setup is simple - I would like to have the user select a month, and have it show both the budget for that month, as well as the cumulative year to date. I have two fields for dates, Year and Month, both of which are (i'm pretty sure) loaded as integers. The date information in my tables are listed in two spots - a column called Period, which has values between 202201 to 202212, and a separate table I've loaded inline
Period:
LOAD * Inline
[
Period, Year, Month
202201, 2022, 1
202202, 2022, 2
202203, 2022, 3
202204, 2022, 4
202205, 2022, 5
202206, 2022, 6
202207, 2022, 7
202208, 2022, 8
202209, 2022, 9
202210, 2022, 10
202211, 2022, 11
202212, 2022, 12
];
The sum for the month is a simple expression, fabs(Sum({ <ProductType = {'Budget'}>} Amount))/1000. When I select a month, I get the budget for that month, so there's no issue there.
I have tried the following
SUM({<PeriodMonth={"<=$(=max(PeriodMonth))"}>} Amount) which returns the value for the selected month only
=Sum({<Period = {">=$('202201')<=$(=maxstring(Period))"}> } Amount) which returns the value for the selected month only
It has been suggested I use Rangesum although I wasn't able to get too far with that approach
And in sheer frustration I tried this mess, which worked for a minute then didn't, and now returns the value for the selected month only
if(GetFieldSelections(Month) = '1', Sum({$<Month = {'1'}> } Amount)/1000,
if(GetFieldSelections(Month) = '2', Sum({$<Month = {'1', '2'}> } Amount)/1000,
if(GetFieldSelections(Month) = '3', Sum({$<Month = {'1', '2', '3'}> Amount) /1000,
if(GetFieldSelections(Month) = '4', Sum({$<Month = {'1', '2', '3', '4'}> } Amount) /1000,
if(GetFieldSelections(Month) = '5', Sum({$<Month = {'1', '2', '3', '4', '5'}> } Amount) /1000,
if(GetFieldSelections(Month) = '6', Sum({$<Month = {'1', '2', '3', '4', '5', '6'}> } Amount) /1000,
if(GetFieldSelections(Month) = '7', Sum({$<Month = {'1', '2', '3', '4', '5', '6, 7'}>} Amount) /1000,
if(GetFieldSelections(Month) = '8', Sum({$<Month = {'1', '2', '3', '4', '5', '6', '7', '8'}> Amount) /1000,
if(GetFieldSelections(Month) = '9', Sum({$<Month = {'1', '2', '0', '4', '5', '6', '7', '8', '9'}> } Amount) /1000,
if(GetFieldSelections(Month) = '10', Sum({$<Month = {'1', '2', '3', '4', '5', '6', '7', '8', '9', '10'}> } Amount) /1000,
if(GetFieldSelections(Month) = '11', Sum({$<Month = {'1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11'}> } Amount) /1000,
if(GetFieldSelections(Month) = '12', Sum({$<Month = {'1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12'}> } Amount)))))))))))))
I feel like I'm missing something fundamental - why is it that each of these are only showing the value for the selected month? This is such a simple thing, but it is fundamental to the work I'm doing, and such a cornerstone of business reporting, that surely there has to be an easy way to resolve this?
