topic Re: Sum Prior and Current Date Help in QlikView

Sum Prior and Current Date Help

Anonymous — Thu, 11 Feb 2016 17:48:49 GMT

Hi All,

I'm trying to get this sum prior and current date working. What I did, I assigned 2 variables for the current and prior date. Please help and see below expression.

Prior Date:

vMinDate=Max({<END_Date={"<$(=MonthStart(Max(END_Date)-1))"}>} END_Date)

Current Date:

vMaxDate=Max({<END_Date={"<$(=MonthStart(Max(END_Date)0))"}>} END_Date)

=sum({<END_Date = {">$(vMinDate)<=$(vMaxDate)"}, [Type]={'Income: Other'}>} [Amount])

where I try to accomplish here is to sum where <= '12/31/15' (vMaxDate) > '11/30/15' (vMinDate) with Type = 'Income: Other'

Many Thanks

Frank

Re: Sum Prior and Current Date Help

sinanozdemir — Thu, 11 Feb 2016 18:01:18 GMT

Hi,

Try with "=" inside the dollar sign expansions:

=sum({<END_Date = {">$(=vMinDate)<=$(=vMaxDate)"}, [Type]={'Income: Other'}>} [Amount])

Re: Sum Prior and Current Date Help

sunny_talwar — Thu, 11 Feb 2016 18:55:32 GMT

Also I would add Date() function around the variables with the required formatting, because set analysis only works when LHS have the same formatting as RHS. Currently your RHS will be converted to Number when you use Max function with it:

Prior Date:

vMinDate = Date(Max({<END_Date={"<$(=MonthStart(Max(END_Date)-1))"}>} END_Date), 'YourDateFormatHere')

Current Date:

vMaxDate = Date(Max({<END_Date={"<$(=MonthStart(Max(END_Date)0))"}>} END_Date), 'YourDateFormatHere')

Re: Sum Prior and Current Date Help

Anonymous — Thu, 11 Feb 2016 18:56:21 GMT

Thanks Sinan!!! works fine with the '=' added

Re: Sum Prior and Current Date Help

Anonymous — Thu, 11 Feb 2016 19:01:01 GMT

Sunny - Thanks for the information.