topic Current Month in QlikView

Current Month

Fri, 22 Jul 2011 14:38:38 GMT

I want to reference the current month in my expression, so that I can sum sales that are current month -1,-2 etc

Can this be done?

Current Month

Fri, 22 Jul 2011 14:43:24 GMT

Hi,

you can get the current month with the expression: month(today())

Hope this help

Regards!

Current Month

swuehl — Fri, 22 Jul 2011 14:47:36 GMT

Yes, should be possible.

you are talking about Sales month to date, previous month to date?

This can be done with set analysis, please have a look into

http://iqlik.wordpress.com/2010/11/27/the-magic-of-set-analysis-point-in-time-reporting/

Or also Set Analysis in the Help file.

Stefan

Current Month

Fri, 22 Jul 2011 14:52:40 GMT

I have tried this but it doesnt work?

sum ({$<SalesDate = {$(=month(today()),-2))

Re: Current Month

Fri, 22 Jul 2011 14:53:08 GMT

I have tried this but it doesnt work?

sum

({$<SalesDate = {$(=month(today()),-2))

Current Month

pduplessis — Fri, 22 Jul 2011 14:56:21 GMT

Is SalesDate a month number or a date? SalesDate cannot = Month# if its a date...

Re: Current Month

Fri, 22 Jul 2011 14:57:35 GMT

sorry missed some of the expression

sum ({$<DisburseDate = {$(=month(today()),-2)

= {Active}, Main = {1}, AgreementAgeDays = {'>=45'}>}Sales)

Current Month

pduplessis — Fri, 22 Jul 2011 14:58:16 GMT

try Sum({$<SalesDate={'$(=something that returns a date in the same format as SalesDate)'}>} Field)

Re: Current Month

Fri, 22 Jul 2011 14:58:29 GMT

yes its a date

Re: Current Month

swuehl — Fri, 22 Jul 2011 14:59:34 GMT

Hi,

your sum misses the expression to sum, and the limitiation of dates might need a change:

like this?

sum({<SalesDate={">=$(=monthstart(today()))"} >} Sales)

Re: Current Month

Fri, 22 Jul 2011 15:00:38 GMT

yes its a date

Re: Current Month

martin59 — Fri, 22 Jul 2011 15:00:59 GMT

Hi,

If you want to uses these kind of expressions, i'll try to help you :

// Current Month Sales
Sum({<[Month]={"$(=Month(Today()))"}>} Sales)
// or
Sum({<[Date]={">=$(=MonthStart(Today())) <=$(=MonthEnd(Today()))"}>} Sales)
// Last Month Sales
Sum({<[Date]={">=$(=MonthStart(Today(),-1)) <=$(=MonthEnd(Today(),-1))"}>} Sales)
// Month-2 Sales
Sum({<[Date]={">=$(=MonthStart(Today(),-2)) <=$(=MonthEnd(Today(),-2))"}>} Sales)

Hope that helps you

Martin

Current Month

pduplessis — Fri, 22 Jul 2011 15:01:16 GMT

try putting =month(today()) in a text object and you will get Jul (depending on your month format...) unless DisburseDate is Jul, you need something else..

Re: Current Month

Fri, 22 Jul 2011 15:06:17 GMT

I am trying this but not working, disburse date is sales date. Basically I want to sum sales from 2 months ago based on todays month

sum({<DisburseDate={">=$(=monthstart(today()-2))"},Status = {Active}, Main = {1}, AgreementAgeDays = {'>=45'} >} SaleAmout)

Re: Current Month

swuehl — Fri, 22 Jul 2011 15:16:00 GMT

The quotation marks and commas are always a bit tricky...

sum({<DisburseDate={">=$(=monthstart(today(),-2))"},Status = {'Active'}, Main = {1}, AgreementAgeDays = {">=45"} >} SaleAmout)

Stefan

Re: Current Month

swuehl — Fri, 22 Jul 2011 15:25:47 GMT

me again,

if you want a rolling two month period, use addmonths(today(),-2) instead of monthstart.

monthstart(today(),-2) --> 01/05/2011

addmonths(today(),-2) ---> 22/05/2011

Stefan

Re: Current Month

Fri, 22 Jul 2011 15:39:13 GMT

Thanks. This working but can you restrict it to just month -2

Current Month

swuehl — Fri, 22 Jul 2011 15:41:55 GMT

Sure,

sum({<DisburseDate={">=$(=monthstart(today(),-2))<$(=monthstart(today(),-1))"},Status = {'Active'}, Main = {1}, AgreementAgeDays = {">=45"} >} SaleAmout)

Stefan

Re: Current Month

Fri, 22 Jul 2011 15:45:45 GMT

See if this gives you what you're looking for,

=sum(if((num(month(disbursedate))>=num(month(today()))-2 and year(disbursedate) = year(today())),SaleAmount))

It is far from sophisticated (and far from complete), but if you start getting the results you're expecting then we can sort out the details.

Current Month

Fri, 22 Jul 2011 15:52:19 GMT

Thanks. works perfect.