Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How can I calculate the sum of actual from the beginning of month till now + the budget from now till the end of month
sum( (from the beginning of month till today())+sum(( from now till the end of month )budget )
SUM({<Date = {">=$(=Date(MonthStart(Today())))<=$(=Date(Today()))"}>}Budget)
+
SUM({<Date = {"<=$(=Date(MonthEnd(Today())))>$(=Date(Today()))"}>}Budget)
Hi jaweher,
so if I understand you correctly, you have a budget that is valid for one month and some sales_data or whatever to calculate against it?
If your sales_data is connected with some kind of date, that shouldn't be a problem - you have the functions monthstart() and monthend().
However, it would be easier to help you achieving whatever you are trying to achieve if you could elaborate a bit on what your >actual< actually is?
Best regards,
DataNibbler
I would suggest ysing the monthstart() and monthend() functions
the month start will get you the first date of the month so you will have your starting point to calculate your actuals.
the monthend function will give you the last date of the month, providing your end point for your budget
below is an example if suming between 2 dates using set analysis
'=Sum({<ShipDate={">=$(vMinEvalRange) <=$(vMaxEvalRange)"}>}PiecesShipped)
you can do something similar where your date range will be the first day of month and today for actuals and then from today to last day of month for budget
SUM({<Date = {">=$(=Date(MonthStart(Today())))<=$(=Date(Today()))"}>}Budget)
+
SUM({<Date = {"<=$(=Date(MonthEnd(Today())))>$(=Date(Today()))"}>}Budget)
the syntax Manish looks to me to be what you need (my info was an example) containing both the monthstart and monthend functions
I am not sure if your requirement needs to add both of those together and one expression may need to total actual and the other budge, depending on your data model