topic Set Analysis in QlikView

Set Analysis

Thu, 03 Mar 2011 21:53:28 GMT

Hello,

The expression as below is woks fine that sum the amount >=vBDate and <=vEDate. How can I change the expression to sum the amount that >=vBDate and <=vEdate-1 (day before the vEdate)?

vBdate and vEDate are import date range.

=Sum({<[Entered Date]={">=$(vBDate)<=$(vEDate)"}>}[SC Amount])

Thanks

Set Analysis

Anonymous — Thu, 03 Mar 2011 22:47:28 GMT

Try this:

Sum({<[Entered Date]={">=$(vBDate)<=$(=vEDate-1)"}>}[SC Amount])

Set Analysis

Thu, 03 Mar 2011 22:49:18 GMT

Hi, you can also do it this way:

Sum({<[Entered Date]={">=$(vBDate)<$(=vEDate)"}>}[SC Amount])

Set Analysis

Fri, 04 Mar 2011 16:15:04 GMT

Hi Michael,

I tried it but it does not work. It ignored the vEdate-1 condition and sum amount that >=vBdate.

Set Analysis

Anonymous — Fri, 04 Mar 2011 17:35:12 GMT

Most likely a formatting issue. When you add -1, the date format converts to integer. This should work:
=sum({$<[Entered Date]={">=$(=date(vBDate))<=$(=date(vEDate-1))"}>} [SC Amount])
I prefer this, it should work the same way:
=sum({$<[Entered Date]={">=$(=date(vBDate))"} * {"<=$(=date(vEDate-1))"}>} [SC Amount])

(I have similar expressions, they do work)

Set Analysis

Fri, 04 Mar 2011 17:48:21 GMT

Thanks Michael,

It works right now.