topic Re: Date range in expression in QlikView

Date range in expression

Tue, 18 Mar 2014 12:54:18 GMT

Hi Guys

I am unable to make out what is wrong in below expression

Sum({<xItem= {'Net Charge'}, D_Date = {">=$(=MonthStart(AddMonths(GDate, -12)))<=$(=MonthEnd(Addmonths(GDate, -1)))"}>} Balance)

I am trying to fetch previous 12 months total of Balance from GDate, where GDate is as column(Dimension)

So if GDate in column is 09/30/2013 than I want Sum(Balance) from 10/31/2012 through 9/30/2013

Your help would be highly appreciated

Thanks

Re: Date range in expression

Tue, 18 Mar 2014 12:55:54 GMT

GDate is Dynamic in this case

Re: Date range in expression

ThornOfCrowns — Tue, 18 Mar 2014 13:02:25 GMT

Is this the same question as: http://community.qlik.com/thread/110890 ?

Re: Date range in expression

Tue, 18 Mar 2014 13:05:18 GMT

try to apply DATE() function with required format on top of monthstart function.

Re: Date range in expression

jerem1234 — Tue, 18 Mar 2014 13:09:01 GMT

My guess is the problem is that set analysis does not work row-wise (meaning the set analysis is applied to the whole set and not individual records). Therefore GDate will not change for different rows in the set analysis. You'll probably have to go with an if statement, maybe like:

Sum({<xItem= {'Net Charge'}>}if(D_Date >= MonthStart(AddMonths(GDate, -12)) and D_Date <= MonthEnd(Addmonths(GDate, -1)), Balance))

Hope this helps!

Re: Date range in expression

PrashantSangle — Tue, 18 Mar 2014 13:11:15 GMT

try this

Sum({<xItem= {'Net Charge'}, D_Date = {">=$(=MonthEnd(AddMonths(GDate, -12)))<=$(=MonthEnd(GDate))"}>} Balance)

Re: Date range in expression

Tue, 18 Mar 2014 13:13:21 GMT

this is what i meant.

Sum({<xItem= {'Net Charge'}, D_Date = {">=$(=DATE(MonthStart(AddMonths(GDate, -12))),'MM/DD/YYYY')<=$(=DATE(MonthEnd(Addmonths(GDate, -1))),'MM/DD/YYYY')"}>} Balance)

Re: Date range in expression

v_iyyappan — Tue, 18 Mar 2014 13:19:32 GMT

Hi,

Try Like this and Please check the date is format correct for Gdate and D_Date. No need to use Addmonths function Instead of use Monthstart and Monthend function.

=Sum({<xItem= {'Net Charge'}, D_Date = {">=$(=MonthStart(GDate, -12))<=$(=MonthEnd(GDate, -1))"}>} Balance)

Regards,

Iyyappan V

Re: Date range in expression

Tue, 18 Mar 2014 14:35:31 GMT

Yes this is the same, but I tried to explain in short

Thanks