Previous Month set analysis not working but with a twist.

[Anonymous]

The twist being that in my calendar I have created serial numbers for my months to simplify the going forward and back in time.

Example, current month =52

Each month from the start of my calendar counts from 1 on.

I am trying t compare current revenue to previous month revenue with Set Analysis.

The two formulas below, I believe should work based on my searches but both return 0.

=sum({<TradMthSerial = {$(=max(TradMthSerial)-1)}>} RevenueAgent)

=sum({<TradMthSerial = {$(=only(TradMthSerial - 1))}>} RevenueAgent)

Thank you for you help.

[#2]

Darrin,

If you throw those expressions from your set definition:

=max(TradMthSerial)-1

=only(TradMthSerial - 1)

into text objects do they evaluate to the results you expect?

[Anonymous]

Clark, thanks for the quick reply.

I do.  with the current month = 52 I receive 51 with both max() and only().  And as I select each prior month I see this value decrease as I would expect.

[whiteline]

Hi.

Try to add quotes:

=sum({<TradMthSerial = {'$(=max(TradMthSerial)-1)'}>} RevenueAgent)

How have you created the  TradMthSerial exactly ?

[Anonymous]

Whiteline, unfortunately that did not solve it.  This seems so simple, not sure what's causing it not to work.

SerialDates:

For the record, this idea comes from Rob Wunderlich.  I cannot take credit.

I have my calendar created, then I sort by DateKey and use AutoNumber() to create my serialized values for weeks, months, quarters, etc.  Typically it simplifies the rolling forward and back quite well.

$(vTableName):

LOAD

    *,    

     AutoNumber(SalesYr & SalesDayOfYr, '_DaySerial') as _SalesDaySerial,

     AutoNumber(SalesYr & SalesWk, '_WkSerial') as _SalesWkSerial,

     AutoNumber(SalesYr & SalesMth, '_MthSerial') as _SalesMthSerial,

     AutoNumber(SalesYr & SalesQtr, '_QtrSerial') as _SalesQtrSerial

Resident tCalendar

Order By DateKey;

[MK_QSL]

Try

AutoNumber(SalesMth, 'SalesMth' ) as _SalesMthSerial

and use

=sum({$<_SalesMthSerial = {'$(=Max(_SalesMthSerial))'}>}RevenueAgent)

[whiteline]

Strange. Actually it should work with or without quotes if the TradMthSerial is just a number (not string or dual).

Have you tried to test with a static value instead of $-sign expansion ?

[#8]

Darrin,

What happens with this expression?

=sum({1<TradMthSerial = {$(=max(TradMthSerial)-1)}>} RevenueAgent)

[Anonymous]

I think I figured out why this was not working. 

I set my list box to show the month short description.  So when I was selecting Apr I would think my formula would work as Apr = 52 and 52-1 = 51 or Mar.

MthShortDesc:  Feb, Mar, Apr

TradMthSerial:   50,51,52

Now if I select 52 directly this formula now works.  Sorry.  But what I would like to do is select it's MthShortDesc equivalent.  Which looks much nicer for the end user.

Would I need to use p()?

[Anonymous]

This was an option I thought might work with this.  But unfortunately not.

=sum({$<TradMthDescShort = p({$<_TradMthSerial={'$(=max(_TradMthSerial)-1)'}>}TradMthDescShort)>} Sales)