topic Display Sum Based on the Largest Value in a Field in QlikView

Display Sum Based on the Largest Value in a Field

Fri, 02 Oct 2009 23:57:42 GMT

Hi,

I'm new to QlikView, so hopefully this is a very basic question that I just haven't been able to figure out the syntax for yet. I want to sum up some data based on a specified week. The week data isn't a calendar week but a field specifying the current year and week of the year (i.e. this week is listed as 200940 and last week was 200939).

I would like to tell the code to figure out the most recent (largest) value in the Week field and then sum up all values that correspond to that particular week. Also, I would like to do the same for last week.

Any guidance you could provide a QlikView newbie would be appreciated.

Thanks,

Scott

Display Sum Based on the Largest Value in a Field

johnw — Sat, 03 Oct 2009 00:25:12 GMT

First, if it isn't already, I would make the underlying numeric value of the week be a real date field so that you can perform real date manipulations on it. To do that, you'd do something like this in your load:

dual(year(Date)&week(Date),weekstart(Date)) as Week

Untested, but I think your expression would then look like this:

sum({<Week={$(=year(max({1} total Week) )&week(max({1} total Week) ))}>} Value)
sum({<Week={$(=year(max({1} total Week)-7)&week(max({1} total Week)-7))}>} Value)

Or for simpler but slower-to-process expressions, or if you aren't on 8.5 or 9.0 yet:

sum(if(Week=max(all Week) ,Value))
sum(if(Week=max(all Week)-7,Value))

I may be making it more complicated than it is, though.

Display Sum Based on the Largest Value in a Field

Sat, 03 Oct 2009 03:04:18 GMT

Thanks, John. I'd forgotten about the All qualifier in the Max statement. My calculation works like a charm now.

Display Sum Based on the Largest Value in a Field

johnw — Sat, 03 Oct 2009 03:10:11 GMT

Glad you got it working. Note that the ALL qualifier MAY go away in a future version. The documentation appears to be pushing us towards using {1} TOTAL instead. ALL seems more clear, though, and I'm kind of hoping they never take it away because of that.