Set Analysis MTD SPLM

[#1]

Hello,

I have a problem with my set analysis formula:

my formula for MTD is :

[Date_Cost Year]={"=$(=max([Date_Cost Year]))"},[Date_Cost Month]={"=$(=max([Date_Cost Month]))"},[Date_Cost Week]=,Date_Cost = {"<=$(=max(Date_Cost))"}

It works good, but my formula for SPLM (Same periode last month) and SPLY (Same period last Year) not works and I don't know why 😞

[Date_Cost]={">=$(=Addmonths(monthstart(max([Date_Cost])),-1)) <=$(=Addmonths(max([Date_Cost]),-1))"}, [Date_Cost Week]=,[Date_Cost Month]= ,[Date_Cost Year]=
[Date_Cost Year]=,[Date_Cost Month]={"=$(=addMonths(max([Date_Cost Month]),-1))"},[Date_Cost Week]=,Date_Cost=
[Date_Cost]={">=$(=AddMonths(Monthstart(max([Date_Cost])),-12)) <=$(=AddMonths(max([Date_Cost]),-12))"}, [Date_Cost Week]=,[Date_Cost Month]= ,[Date_Cost Year]=

Can you help me please is very urgent [:S]

[#2 · Accepted solution]

I'm guessing the AddMonths function is messing with the date formats. It's a common problem in Set Analysis. You probably need to use Date() to format the date after the AddMonths functions.

A good way to test it is to put your expressions into a straight table chart, but don't give them a label. Then when the chart is rendered, the label will be the set analysis expression with the dollar sign expansions evaluated. This will probably show you that the date formats are different than your first expression.

...={">=$(=Date(Addmonths(monthstart(max([Date_Cost])),-1)), 'MM/DD/YYYY')...

or whatever your actual date format is.

[#3]

Thanks a lot 🙂

It's working, Set analysis formulas are very complicate but when it works , it's very usefull 🙂

MTD [Date_Cost Year]={"=$(=max([Date_Cost Year]))"},[Date_Cost Month]={"=$(=max([Date_Cost Month]))"},[Date_Cost Week]=,Date_Cost = {"<=$(=max(Date_Cost))"}
SPLM Date_Cost={">=$(=Date(AddMonths(min(Date_Cost),-1),'YYYY-MM-DD')) <=$(=Date(AddMonths(max(Date_Cost),-1),'YYYY-MM-DD'))"}, [Date_Cost Week]=,[Date_Cost Month]= ,[Date_Cost Year]=
SPLY Date_Cost={">=$(=Date(AddMonths(min(Date_Cost),-12),'YYYY-MM-DD')) <=$(=Date(AddMonths(max(Date_Cost),-12),'YYYY-MM-DD'))"}, [Date_Cost Week]=,[Date_Cost Month]= ,[Date_Cost Year]=

YTD [Date_Cost Year]={"=$(=max([Date_Cost Year]))"},[Date_Cost Month]=,[Date_Cost Week]=,Date_Cost = {"<=$(=max(Date_Cost))"}

Thank you very much 🙂