topic Re: Help with TTM in QlikView

Help with TTM

Fri, 20 Sep 2013 13:13:10 GMT

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.

Re: Help with TTM

swuehl — Fri, 20 Sep 2013 13:16:30 GMT

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.

Re: Help with TTM

abeyphilip — Fri, 20 Sep 2013 13:20:26 GMT

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

Re: Help with TTM

Fri, 20 Sep 2013 13:33:16 GMT

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.

Re: Help with TTM

abeyphilip — Fri, 20 Sep 2013 13:41:17 GMT

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

Re: Help with TTM

Fri, 20 Sep 2013 13:59:03 GMT

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

Re: Help with TTM

Fri, 20 Sep 2013 14:00:51 GMT

This does not work

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

But this does work

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

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

Re: Help with TTM

swuehl — Fri, 20 Sep 2013 14:08:00 GMT

Your issue is probably just about correct value matching.

If your expression gives you something like 'Jun 2013', this must exactely match the text representation of a value in your field CalendarWrittenMonthAndYear. If, for example, your value is slightly different (having for example 4 character month name and no space like in 'Sept2013' shown in your original post), there will be no match.

Re: Help with TTM

Josh_Good — Fri, 20 Sep 2013 18:14:09 GMT

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

Re: Help with TTM

Mon, 23 Sep 2013 12:15:09 GMT

Thanks to all for this, I learned a lot and it is now working!!!