topic Re: How to show Previous Month and Last Year Previous Month in a straight table? in QlikView

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

neena123 — Tue, 22 Dec 2015 15:45:53 GMT

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

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

lironbaram — Tue, 22 Dec 2015 15:57:21 GMT

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

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

neena123 — Tue, 22 Dec 2015 15:59:38 GMT

Hi Liron!

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

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

miguelbraga — Tue, 22 Dec 2015 16:37:36 GMT

Hi Neena,

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

Regards,

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

neena123 — Tue, 22 Dec 2015 18:37:34 GMT

It did not work for me. Thanks though!

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

maxgro — Tue, 22 Dec 2015 19:52:04 GMT

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)

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

neena123 — Wed, 23 Dec 2015 16:10:34 GMT

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

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

maxgro — Wed, 23 Dec 2015 18:45:13 GMT

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

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

neena123 — Thu, 24 Dec 2015 15:18:02 GMT

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?