How to show Previous Month and Last Year Previous Month in a straight table?

[neena123]

I need to display the sales for Previous Month and Last Year Previous Month. Nothing seems to work for me. I also have some conditions that the sales needs to be summed up by.

My expression for Last Year Previous Month

SUM(MonthStart(Today()-13) AND IF(MATCH([Object Account],'5311','5312','5314','5315','5317') ,Sales))

My expression for Current Year Previous Month

SUM(MonthStart(Today()-1) AND IF(MATCH([Object Account],'5311','5312','5314','5315','5317') ,Sales))

[lironbaram]

hi you need to tweek your expressions like that

SUM({<MonthField={"$(=MonthStart(addmonths(Today(),-13)))"}>} IF(MATCH([Object Account],'5311','5312','5314','5315','5317') ,Sales))

and

for previous month

SUM({<MonthField={"$(=MonthStart(addmonths(Today(),-1)))"}>} IF(MATCH([Object Account],'5311','5312','5314','5315','5317') ,Sales))

in order for this to work

you need to create a monthyear field that hold the  month start date for each date

[neena123]

Hi Liron!

Thanks for the quick reply! Where would I create a monthyear field?

[miguelbraga]

Hi Neena,

You can create that field in the same table where the date field is

Regards,

MB

[neena123]

It did not work for me. Thanks though!

[maxgro]

some test data

Calendar:

load

  Date,

  12*(Year(Today())-Year(Date)) + Month(Today()) - Month(Date) as MonthsAgo;

load

  date(MakeDate(2014)+IterNo()-1) as Date

AutoGenerate 1

While IterNo() <= 730;

Fact:

LOAD

  date(makedate(2014) + ceil(rand()*730)) as Date,

  rand()*100 as Sales,

  pick(ceil(rand()*5),'5311','5312','5314','5315','5317', '1') as [Object Account] 

AutoGenerate 1000;

then in a straight table chart you can use Date as Dimension

and this expression for november 2014 sales

SUM({$ <MonthsAgo={13},[Object Account]={5311,5312,5314,5315,5317}>} Sales)

or this for november 2015 sales

SUM({$ <MonthsAgo={1},[Object Account]={5311,5312,5314,5315,5317}>} Sales)

[neena123]

I added in the MonthsAgo field but the expression isn't working for me...all it returns are 0s

[maxgro]

Did you add the Calendar table (associated with your table) with the MonthsAgo field?

[neena123]

Attached is my data model. I should mention that my Month field from my Final table is actually not a Month field. It was the individual sales amount fields for each month. I grouped the 12 individual sales fields and added them under the Month field. Would that be the reason why the above expression is not working?