Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
neena123
Partner - Creator
Partner - Creator

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

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))

8 Replies
lironbaram
Partner - Master III
Partner - Master III

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
Partner - Creator
Partner - Creator
Author

Hi Liron!

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

miguelbraga
Partner - Specialist III
Partner - Specialist III

Hi Neena,

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

Regards,

MB

neena123
Partner - Creator
Partner - Creator
Author

It did not work for me. Thanks though!

maxgro
MVP
MVP

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
Partner - Creator
Partner - Creator
Author

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

maxgro
MVP
MVP

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

neena123
Partner - Creator
Partner - Creator
Author

Data Model.PNG

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?