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.
=sum({<TradMthSerial = {'=TradMthSerial=$(=max(TradMthSerial)-1)'}>} RevenueAgent)
hi once try this
sum({<TradMthSerial = {$(=max({<Year={$(=Year(addmonths(max(Date),-1)))}>}TradMthSerial)-1)}>} RevenueAgent)
Hai Darrin ,
U May Try To Create the Variable For Month
Previous Month
= Date#(Date(MonthStart(addmonths(max(Include Date Field),-1)),'MMM'))
And Then You Call Your Expression
Interesting,
Both Whiteline and Prem's formulas give the same value as the current month selected. Not the prior, which is odd based on what I am seeing in the script.
This actually works perfectly if I select the _TradMthSerial value.
=sum({<_TradMthSerial = {$(=only(_TradMthSerial - 1))}>} RevenueAgent)
We need to select TradMthDescShort and have it grab the prior month. Select Apr and it pulls _TradMthSerial = 51.
TradMthDescShort | _TradMthSerial | RevenueAgent |
Feb | 50 | 431 |
Mar | 51 | 328 |
Apr | 52 | 285 |
Thanks for everyone's help. This community is awesome.
But still not working when selecting the Month Description.
Any other thoughts?
Could you upload the sample that is not working ?
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
Jonathan, winner winner chicken dinner. That did it. Thank you very much.
So I understand, can you explain how comma then rest works. I don't think i have seen the set written that way before.
Darrin
The , just separates the selection for the two fields, maybe you meant the = sign. An = sign with nothing on the RHS instructs the set to ignore the selections for that field (in this case, TradMthDescShort).
Jonathan
Actually that is what it was. I am familiar with the open ended "=" sign meaning to ignore but I hadn't thought about it in this usage. Using it to select a value but disregard the selection.
Typically I would use it on a global scale for dates, etc. on a higher level summary.
Thanks again.