Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to calculate a running total of 12 month SALES in my application based on the selected year and month.
For this purpose I have created to variables (to make expression a bit more simple):
vSelectionMonthEnd = monthend(date#('01.' & CALENDER_MONTH & '.' & CALENDER_YEAR,'DD.MM.YYYY'))
-> This variable holds the month end date of current selected monht and year.
vSelectionBeginRegion = monthstart(addmonths(monthend(date#('01.'&CALENDER_MONTH & '.' & CALENDER_YEAR,'DD.MM.YYYY')),-11))
-> This variable holds the start date of 12 months before.
I have following expression in my straight table:
=sum({<DATE={">=$(#vSelectionBeginRegion) <=$(#vSelectionMonthEnd)"}>} SALES)
This seems to work fine.
Now I would like to show also running 12 months sales for previous month, in other words: if SEP-2010 is selected, expression above will sum sales for 1.10.2009 - 30.9.2010. Next column should show running 12 month total for AUG-2010 (SEP-2010 still selected...), that is sales for period 1.9.2009 - 31.8.2010.
To get this, I add a new expression in my straight table:
=sum({<DATE={">=AddMonths($(#vSelectionBeginRegion),-1) <= AddMonths($(#vSelectionMonthEnd),-1)"}>} SALES)
This expression returns zero, which is not correct.
Any ideas how to get the later expression to work?
-- Jussi
But I would prefer to simplify this problem like this:
Variable:
Let vSelectedDate = Date#(CALENDER_MONTH & '.' & CALENDER_YEAR,'MM.YYYY');
Expression:
=Sum({<DATE={">=$(=MonthStart(vSelectedDate,-12)) <=$(=MonthEnd(vSelectedDate))"}>} SALES)
You might need to play a bit with the date formats to get this to work. DATE and MonthStart/MonthEnd must all be in the same date format.
Jonathan
I'm guessing the AddMonths function is changing the format of your date. Try doing Date(AddMonths(var, -1), 'DateFormatNeeded')
One way to check this is to create a straight table. Add your second expression, but don't give it a label. Then, when the chart is rendered, the label will be your expression with the dollar sign expansion evaluated. If you add both of your expressions, I bet the date format will differ on them (the second one may even show a number rather than a date).
Hi
I would try
=sum({<DATE={">=$(=AddMonths(vSelectionBeginRegion,-1)) <=$(=AddMonths(vSelectionMonthEnd,-1))"}>} SALES)
Jonathan
But I would prefer to simplify this problem like this:
Variable:
Let vSelectedDate = Date#(CALENDER_MONTH & '.' & CALENDER_YEAR,'MM.YYYY');
Expression:
=Sum({<DATE={">=$(=MonthStart(vSelectedDate,-12)) <=$(=MonthEnd(vSelectedDate))"}>} SALES)
You might need to play a bit with the date formats to get this to work. DATE and MonthStart/MonthEnd must all be in the same date format.
Jonathan
Thanks Jonathan, this one solved the problem!
-- Jussi