sum for previous month/(previous YearMonth)

[#1]

Hello All

I am facing issue with below requirement.

I have a expression as 1)    sum(  {<Type={'123'}>}   sales).

This give me the sum for YearMonth selected.

I need to create one more field which will provide the sum for previous month/(previous YearMonth).

I am getting that using addmonths(YearMonth, -1).  However I am getting error while I integrate this in above expression (1).

I am trying something like   -- >  sum(  {<Type={'123'}, YearMonth= addmonths(YearMonth, -1) >}   sales). 

But it is not working .

Please help me correct above expression. In Expression 1) I need to add logic for previous YearMonth ...

Thanks in advance....

[sunny_talwar]

Are you using this in a chart where YearMonth is a dimension?

[maxgro]

maybe you need to format the result of addmonths as your YearMonth field, example:

sum({$ <Type={'123'},  YearMonth={'$(=date(addmonths(max(YearMonth),-1),'YYYY MM'))'}>} sales)

[pamaxeed]

Hi,

i think it is a formatting problem like maxgro stated.

Try to give a look to the sample attached, I am using a variable to hold the previous month in the following format:

YYYY-MM.

Cheers,

Patric

[simenkg]

I usually do this by creating a MonthCounter-field in my master calendar.

Calendar:

load %Datekey,

%Datekey as Date,

Year(%DateKey)&'-'&Month(%Datekey) as YearMonth,

Autonumber(Year(%DateKey)&'-'&Month(%Datekey),'MonthCounter') as MonthCounter

resident tempDates order by %DateKey asc;

Then you can use the expression

sum({$ <Type={'123'},  YearMonth=, MonthCounter={$(=Max(MonthCounter)-1)}>} sales)

Regards

SKG

[Anonymous]

Yes. I am using in chart as a Set analysis expression.

[Anonymous]

Thanks a lot Maxgro.. It worked for me with slight change. 

Can you please explain me how/where  to use the $ and '=' which u used before date.  I do not understand how to write these set analysis explression. 

[maxgro]

dollar sign expansion

I can't explain better than Henric

The Magic of Dollar Expansions