Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi
If I understand correctly, you are making a selection in TradMthDescShort - so you will need to override this selection in your set expression. Something like:
=sum({<TradMthDescShort= ,TradMthSerial = {$(=max(TradMthSerial)-1)}>} RevenueAgent)
HTH
Jonathan
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?
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.
Hi.
Try to add quotes:
=sum({<TradMthSerial = {'$(=max(TradMthSerial)-1)'}>} RevenueAgent)
How have you created the TradMthSerial exactly ?
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;
Try
AutoNumber(SalesMth, 'SalesMth' ) as _SalesMthSerial
and use
=sum({$<_SalesMthSerial = {'$(=Max(_SalesMthSerial))'}>}RevenueAgent)
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 ?
Darrin,
What happens with this expression?
=sum({1<TradMthSerial = {$(=max(TradMthSerial)-1)}>} RevenueAgent)
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()?
This was an option I thought might work with this. But unfortunately not.
=sum({$<TradMthDescShort = p({$<_TradMthSerial={'$(=max(_TradMthSerial)-1)'}>}TradMthDescShort)>} Sales)