how to calculate all period till max date selected

[mustafaelryah]

Hello all,

I defined max selected date in variable vMaxTransDate  and it works correctly

but I have set analysis in chart i need it to calculate all dates <= vMaxTransDate

Also I have already set analysis on it as shown below

FABS(Sum({$<Account_Budget -= {'NON-Budgeted','-','0'}>} balance))

how can I add  code to set analysis above to calculate all period till vMaxTransDate  where is the date field is Trans_date

Best Regards.

[Colin-Albert]

Make sure that the Trans_date field and your variable vMaxTransDate are formatted the same then this should work

FABS(Sum({$<Account_Budget -= {'NON-Budgeted','-','0'}, Trans_date = {"<=$(vMaxTransDate)"}>} balance))

[ahaahaaha]

Hi,

May be like this

FABS(Sum({$<Account_Budget -= {'NON-Budgeted','-','0'}, FieldDate={"<=$(vMaxTransDate)"}>} balance)) Or

FABS(Sum({$<Account_Budget -= {'NON-Budgeted','-','0'}, FieldDate={"<=$(=vMaxTransDate)"}>} balance))

[mustafaelryah]

Colin thanks it works till max. But they excluded the beginning my data is from 2010 when I select the  2017 till JAN it will excluded all previous years. I need the old dates from beginning till the maxdate.

so in other words I need it from min transDate in the data till the max date selected.

Looking forward.

Best Regards.

[mustafaelryah]

Andrey thanks it works till max as Colin answer .  But they excluded the beginning. My data is from 2010 when I select the  2017 till JAN it will excluded all previous years. I need the old dates from beginning till the maxdate.

So in other words I need it from min transDate in the data till the max date selected.

Looking forward.

Best Regards.

[anlonghi2]

Hello Mustafa,

if you don't have to consider filters other than vMaxTransDate you can put a 1 instead of $ in your set analysis.

Best regards.

[mustafaelryah]

Thanks andrea. But it shown no data selected

[sasiparupudi1]

May be like this

=FABS(Sum({$<Account_Budget -= {'NON-Budgeted','-','0'}, FieldDate={">=$(=min({1}TransDate)) <=$(=vMaxTransDate)"}>} balance))

hth

[sunny_talwar · Accepted solution]

Seems like you are making selection Year and Months field.... you need to ignore selections in those fields since date will be driving all your results here... try this

FABS(Sum({$<Account_Budget -= {'NON-Budgeted','-','0'}, Trans_date = {"<=$(vMaxTransDate)"}, Year, Month, MonthYear, Quarter, QuarterYear, Week>} balance))

I added all these fields in red, but you only need to add those fields which user are going to make selection in.

[mustafaelryah]

sasiparupudi1   thanks. Not working, Check the correct answer above