Using variables and functions in set analysis

[#1]

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

[#2]

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).

[jonathandienst]

Hi

I would try

=sum({<DATE={">=$(=AddMonths(vSelectionBeginRegion,-1)) <=$(=AddMonths(vSelectionMonthEnd,-1))"}>} SALES)

Jonathan

[jonathandienst · Accepted solution]

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

[#5]

Thanks Jonathan, this one solved the problem!

-- Jussi