7 Replies Latest reply: Feb 24, 2011 11:52 AM by Matthew Riedl RSS

    Aggregate Expression for the most current time frame

      I am new to qlikview and have searched the documentation for an example of an expression that calculates the total for the most current time frame.

      In this case, I would like sum the sales for a week to display in a chart. The fields from my database provide: "Sales", "Day", "Week End Date"

      I would like to get the Max Week End Date and total Sales for all "Days" of that week. Both "Day" and "Week End Date" fields are formatted MM/DD/YYYY. Could someone walk me through the creation of the expression?

      Thanks!

        • Aggregate Expression for the most current time frame
          John Witherspoon

          The best (good combination of performance and "simplicity") way to handle this is probably with set analysis. I believe you'd do this:

          dimension = Day
          expression = sum({<"Day"=,"Week End Date"={'$(=max({1} "Week End Date"))'}>} Sales)

          The dimension should be obvious, but there's a lot to explain in the expression, and I don't guarantee I have it 100% correct.

          The basic form we're using here is this:

          sum({set expression} Sales)

          So we're summing Sales for some given set of data that we've defined. In your case, you want to sum sales for the most recent week, so the set expression must define the most recent week. There's a little more to it, but we'll get there.

          As you say, you want the max week end date, which in QlikView is max("Week End Date"). You can see that tucked away in the set expression, but it does a little more than that. It's written like this:

          max({1} "Week End Date")

          So what's the {1} doing? That is yet another set expression. It says to ignore ALL selections, so to use the set that includes ALL dates. For us, what's important is that we include ALL "Week End Date" values. If someone selects some week five years ago, I assume you still want for this specific chart to show data from the most recent week available, not from that week five years ago. So that's what the {1} is doing.

          Working out from there, we see that there's a $(=...) wrapped around it. That's called "dollar sign expansion". The expression in the dollar sign expansion will be evaluated FIRST, before anything else, and ignoring all of your chart's dimensions. The result of that evaluation will be LITERALLY inserted into the expression before the rest of the expression is evaluated. So let's say your max week end date is 02/06/2011. QlikView will do the dollar sign expansion, and return this expression:

          sum({<"Day"=,"Week End Date"={'02/06/2011'}>} Sales)

          It will then evaluate it as if you'd typed THAT expression instead of the expression you actually typed.

          The single quotes are there because QlikView is doing a string comparison to your date. I could explain why, but it's complicated and kind of immaterial. Suffice to say that it simply doesn't know how to compare dates to dates in set analysis other than by their text equivalents (even though it knows how to do it in an if() statement, for instance).

          Now, what's that "Day"= part of the expression doing there? Let's assume that your user has selected some day five years ago. Unless we tell QlikView to ignore that selection, it will try to evaluate the intersection of that day five years ago and the week ending 02/06/2011, and return nothing. So we tell it to ignore any selection in Day. In your real application, you may have other date fields connected to the week end date, like a year, a month, a quarter. You'd have to list ALL of those date fields here, following the same pattern used for Day, because we want to ignore them all.

          The final result of all that set analysis is something fairly simple - QlikView behaves, for the purpose of this expression, as if you'd actually selected the week ending date of 02/06/2011. So it will only return a value for sum(Sales) for Days that are in that week, and all other days will be excluded from the chart.

          One possible issue is if you have other expressions in the chart. Let's say you have a quantity you want to see as well, and just do sum(Quantity). That won't work, as that expression will ignore the set from the other expression, and just return, for example, the quantity for some day five years ago that the user selected. So you would need to repeat your set analysis expression for ALL expressions in the chart.

          And as I said, I may have made a mistake. But if so, with any luck, the explanation will allow you to debug my code. If not, let me know, and I'll make sure I get a working expression for you.