Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
mustafaelryah
Creator
Creator

how to calculate all period till max date selected

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.

1 Solution

Accepted Solutions
sunny_talwar

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.

View solution in original post

10 Replies
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
Partner - Master
Partner - Master

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

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

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

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

Thanks andrea. But it shown no data selected

sasiparupudi1
Master III
Master III

May be like this

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

hth

sunny_talwar

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