topic Re: How to retrieve max record for a given month or year? in QlikView

How to retrieve max record for a given month or year?

Tue, 29 Jul 2014 00:02:08 GMT

Hi All,

I have an example table as the following:

Customer table:

Transaction Date, Channel Code, Beginning Balance, Order Value, Ending Value

4/25/2014 CH1 100 30 130

4/28/2014 CH1 130 10 140

4/30/2014 CH1 140 20 160

5/15/2014 CH1 140 20 160

5/31/2014 CH1 160 20 180

I have other dimensions year, month, day, and channel. So, my question is how can I retrieve the max record for a given month or year? I mean in case I choose month of April 2014 I want the table to show only the record associated with the 4/30/2014?

Thanks!

Re: How to retrieve max record for a given month or year?

rwunderlich — Tue, 29 Jul 2014 00:10:47 GMT

In most cases, you can use the FirstSortedValue function.

=FirstSortedValue([Ending Value], -[Transaction Date])

-Rob

Re: How to retrieve max record for a given month or year?

Tue, 29 Jul 2014 16:48:30 GMT

Thanks Rob, however, applying that raised an error "Error in calculated dimension". On the other hand, Isn't that function retrieve the first value? cause what i'm looking at is the last one instead.

Thanks again, Louie

Re: How to retrieve max record for a given month or year?

JonnyPoole — Tue, 29 Jul 2014 17:15:23 GMT

I changed the chart expression to reference the Maximum Date in the selection as follows

sum( {$<Date={'$(vMaxDate)'}>} Sales)

....where vMaxDate is a variable with this definition: =Date(max(Date),'MM/DD/YYYY')

This focuses the chart to just show records for the max date of the selection.

in this example, Date is my date field from the data source which in a list box renders as MM/DD/YYYY values

Re: How to retrieve max record for a given month or year?

rwunderlich — Tue, 29 Jul 2014 18:22:40 GMT

I was expecting that you would use this as an Expression, not a Dimension.

The "-" in the second parm selects the last vs the first value.

Re: How to retrieve max record for a given month or year?

Tue, 29 Jul 2014 18:29:24 GMT

Well, I tried it as an Expression but it showed everything (the filter didn't work) therefore I tried the Dimension thing!

Re: How to retrieve max record for a given month or year?

Tue, 29 Jul 2014 20:20:37 GMT

It has worked now... I had other columns that were listed as dimensions as well. I removed them and voila! Thank you Rob!