topic Re: Sum and Filtering using dates in QlikView

Sum and Filtering using dates

Mon, 20 Apr 2015 14:49:20 GMT

I am attempting to create an expression that allows me to sum a value based on the max date. I am able to successfully get this to work with the following:

BS Fiscal Year Column = 2014

Sum({$<"BS Fiscal Year"=

{$(=max("BS Fiscal Year"))}>}

[Total Adjusted Fair Value])

When attempting to do the same, but using a date formatted MM/DD/YYYY

BS Date Column = 12/31/14

Sum({$<"BS Date"=

{$(=max("BS Date"))}>}

[Total Adjusted Fair Value])

I am not getting any results. Any idea why?

Re: Sum and Filtering using dates

sunny_talwar — Mon, 20 Apr 2015 14:52:17 GMT

Try this instead:

=Sum({$<[BS Date]= {"$(=Date(Max([BS Date]), 'DD/MM/YYYY'))"}>} [Total Adjusted Fair Value])

HTH

Best,

Sunny

Re: Sum and Filtering using dates

Mon, 20 Apr 2015 15:00:16 GMT

Sunny,

Thanks for the prompt reply, but this did not work.

Re: Sum and Filtering using dates

sunny_talwar — Mon, 20 Apr 2015 15:01:54 GMT

Little typo, this may still not work, but give it a shot

=Sum({$<[BS Date]= {"$(=Date(Max([BS Date]), 'MM/DD/YYYY'))"}>} [Total Adjusted Fair Value])

Re: Sum and Filtering using dates

Mon, 20 Apr 2015 15:04:59 GMT

Sunny,

Thank you. That one worked.

Re: Sum and Filtering using dates

sunny_talwar — Mon, 20 Apr 2015 15:06:20 GMT

Cool, Stupid mistake on my end, but glad it got quickly resolved.

Best,

Sunny