Date range in expression

[#1]

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

[#2]

GDate is Dynamic in this case

[ThornOfCrowns]

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

[#4]

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

[jerem1234]

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!

[PrashantSangle]

Hi

try this

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

[#7]

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)

[v_iyyappan]

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

[#9]

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

Thanks