Solved: Help with TTM - Qlik Community

Report Inappropriate Content · ‎2013-09-20

sum ({$<CalendarWrittenMonthAndYear={[Sept2013]}>} SalesAmount) seems to work great, but if I want the modifier to be based on todays month - 1 how can I do that.

I want the formula to automatically put in last month, I am having trouble with that.

Josh_Good · ‎2013-09-20

I think the reason your expression isn't work is because it returns only the text and not the text surrounded by quotes.

i.e. in your dollar sign expansion you are getting

Jun 2013

when you need

'Jun 2013'

Chr(39) will return a single quote in an expression

Try:

sum{$<CalendarWrittenMonthAndYear={$(=chr(39) & MonthName(MonthStart(AddMonths(Floor(ReloadTime()),-3))) & chr(39) )}>}SalesAmount)*-1

View solution in original post

swuehl · ‎2013-09-20

Try something like

sum ({$<CalendarWrittenMonthAndYear={'$(=Date(today(),'MMMYYYY))'}>} SalesAmount)

To make this work, your MonthNames system variable (check your load script, at the top) should have the same format as your Month names in your field.

abeyphilip · ‎2013-09-20

Hi,

Create a variable like:

vPrevMonth = MonthName(addmonths(today(),-1))

The you can use that variable in the set analysis

sum ({$<CalendarWrittenMonthAndYear={$(vPrevMonth)}>} SalesAmount)

Regards,

Abey

Report Inappropriate Content · ‎2013-09-20

Still having trouble

This expression works great

=MonthName(MonthStart(AddMonths(Floor(ReloadTime()),-3)))

It gives me the correct date of Jun 2013

I want to use that in this expression, but it doesn't want to work

sum{$<CalendarWrittenMonthAndYear={$(=MonthName(MonthStart(AddMonths(Floor(ReloadTime()),-3))))}>}SalesAmount)*-1

I am trying to get qlikview give me the previous months sales in a pivot table. So in the example above I want it to give me sales for Jun 2013.

I am very much a beginner and not sure where I am going wrong.

abeyphilip · ‎2013-09-20

Hi,

You may have to assign this to a variable and then use that variable in your expression as mentioned in my previous post.

To add a variable,

Settings -> Variable Overview -> Add

Regards,

Abey

Report Inappropriate Content · ‎2013-09-20

It does not want to work with the Month Year, when I replace the variable to be a year 2012, then the formula works, but when I make it MonthandYear - Aug 2013 it gives me a blank.

Lance H. James, CPA

Director of Finance

The Aspen Brands Company

Kate Aspen • Baby Aspen

www.kateaspen.com<http://www.kateaspen.com> • www.babyaspen.com<http://www.babyaspen.com>

T: 678.282.0053 ext. 777

2400 Chattahoochee Drive

Duluth, GA 30097

2013, 2012 & 2011 Top Places to Work - Atlanta Journal Constitution

Follow us on Facebook and Twitter!

Report Inappropriate Content · ‎2013-09-20

This does not work

sum({$<CalendarWrittenMonthAndYear={$(vPrevMonth)}>} SalesAmount)

But this does work

sum({$<CalendarWrittenYear={$(vYear)}>} SalesAmount)