Partial Sums and Date formats - - Qlik Community

Anonymous · ‎2011-02-10

This morning I struggled to figure out why my I could not seem to get partial sums by MyDate. I have a report with MyDate, MyTIME and one measure. Whenever I tried to get partial sums by MyDate, it would do partial sums by MyDate and MyTIME. I think the problem is due to the way Qlikview stores MyDate. The Date comes from a date time database attribute. The formula is like this

date(ConvertToLocalTime(date('01/01/1970','MM/DD/YYYY hh:mm:ss') +(TIME/(60*60*24)),'Central Time (US & Canada)'),'YYYY/MM/DD') as MyDATE

The time formulas is like this:

time(ConvertToLocalTime(date('01/01/1970','MM/DD/YYYY hh:mm:ss') +(TIME/(60*60*24)),'Central Time (US & Canada)'),'hh:mm:ss tt') as MyTIME

I think the new attributes I am creating "appear" one way for display but function another way for partial sums which is frustrating. If I instead create a string from my date such as

year(date(ConvertToLocalTime(date('01/01/1970','MM/DD/YYYY hh:mm:ss') +(TIME/(60*60*24)),'Central Time (US & Canada)'),'hh:mm:ss tt')) & '-' &
month(date(ConvertToLocalTime(date('01/01/1970','MM/DD/YYYY hh:mm:ss') +(TIME/(60*60*24)),'Central Time (US & Canada)'),'hh:mm:ss tt')) & '-' &
Day(date(ConvertToLocalTime(date('01/01/1970','MM/DD/YYYY hh:mm:ss') +(TIME/(60*60*24)),'Central Time (US & Canada)'),'hh:mm:ss tt')) as MyOtherDate

...then my partial sums work by MyOtherDate. Just wanted to posted this as a potential gotcha that you have to be careful to avoid. My opinionis that it would be "better" if partial sums worked on what was displayed on the report rather than what is stored behind the scenes. I cant think of any reason why you would want to display the data one way then have it treat the data as if it was a different value for the purposes of partial sums.

johnw · ‎2011-02-10

I think you're very close to correct about what's going wrong. Your attributes DO appear one way for display, but are actually something different.

When you use the date() or time() function, those are FORMATTING functions. They only tell the system how to display the data. So the underlying data value of both your dates and times still includes both the dates and times. They are the same value. Both are timestamps. (Unless I'm wrong, but I think that's how it works, and it's consistent with what you're seeing.)

To fix it, make use of the floor() and frac() functions. There are other functions you can use, like daystart(), but I suspect floor() and frac() are the most commonly-used for this.

date(floor(ConvertToLocalTime(date('01/01/1970','MM/DD/YYYY hh:mm:ss') +(TIME/(60*60*24)),'Central Time (US & Canada)')),'YYYY/MM/DD') as MyDATE

time(frac(ConvertToLocalTime(date('01/01/1970','MM/DD/YYYY hh:mm:ss') +(TIME/(60*60*24)),'Central Time (US & Canada)')),'hh:mm:ss tt') as MyTIME

I can think of plenty of reasons why you'd want to display data one way and use a different underlying value. As a quick example, I usually do this:

date(monthstart(Date),'MMM YYYY') as "Month"

I want my "Month" to remain a date so that I can use date functions on it, so that it sorts correctly despite having the month name first, and so on. But I sure don't want to display Dec 2010 as 01/12/2010 in my charts, drop down lists, or anything else.

Consider also rounding for display purposes. Just because you want to round off the displayed numbers doesn't mean you want to add up the rounded values for your total. You typically want to add up the underlying values instead, and THEN round the total. If you don't, well, that's what the round() function and "sum of rows" is for. Options are good.

Anonymous · ‎2011-02-10

John! Thanks. Your suggestion is a lot cleaner than the date string I built and works fine. Couple of things. I agree with you I too can think of plenty of reasons why you'd want to display data one way and use a different underlying value. However, what i said was a wee bit more specific. How often do you want to display an attribute one way, then have the partial sums "function/property" act against a stored value rather than what you displayed? Its a subtle but important difference!

johnw · ‎2011-02-11


Tim Webber wrote:what i said was a wee bit more specific. How often do you want to display an attribute one way, then have the partial sums "function/property" act against a stored value rather than what you displayed? Its a subtle but important difference!

True, that's an important difference and I didn't catch your specificity the first time around. But even taking that into account, I guess I just have a different opinion. Yes, what QlikView does can be confusing. You're looking right at a date. It's obviously a date. And yet QlikView is obviously totaling by date incorrectly, with separate totals for revery single time.

But the field isn't actually a date. It's a timestamp. So you're REALLY subtotaling by a timestamp, and QlikView is doing that, in my opinion, correctly. If you want to subtotal by date, you should use a date field. If you really must use a timestamp field, but want to override the behavior in that one chart, you can use date(floor(Timestamp)) to get the behavior you want.

If QlikView did that for you automatically based on simply the display format, then what could you do to get timestamp behavior for this timestamp field if you wanted it? You'd lose that ability. So it's not a matter of "how often do you want to do this" but rather, "do you ever want to do this." I'll admit I'm not thinking of an example where I'd want to do it, but I'd be very surprised if there aren't a number of charts out there in the wild taking advantage of this behavior for whatever reason.

So I think QlikView's approach gives me maximum control and flexibility. I like maximum control and flexibility. The trade off, of course, is that it can be confusing, and it can be harder to get the desired behavior out of it. That's not necessarily in QlikTech's or many users' best interest, even if I like it personally.

But I do see using the display format for partial sums as a slippery slope to a different kind of confusion. Right now, I think I can count on QlikView to behave as follows: For display, use the display format. For all other purposes, use the underlying value of the field. That's a simple, easily understood rule. If we start allowing exceptions, start having other things like partial sums use the display format instead, we can end up with a different kind of confusion, a "Wait, how does QlikView treat my field in this context?" confusion. (Though I'll admit we've already slipped a bit down the slope in THAT regard.)

Besides, I think the real source of the confusion isn't so much the chart behavior, but rather how the date() and time() functions work. It's too late now due to backwards compatibility issues, but I think I'd have had these functions automatically convert the underlying data to a date or time rather than JUST be formatting functions. I'd force users to use timestamp() if they wanted to preserve it as a timestamp. They could still format their timestamp as a date if they wanted, but the use of timestamp() in the field definition would be a clear indication of what the field actually was.

I assume you thought you really had date and time fields after applying date() and time() functions to them, and in my opinion, you should have been right, and so you should never have had the chart problem in the first place. Too late to fix that QlikView design flaw, unfortunately. Though maybe someone else could explain to me why it makes sense that date() and time() don't actually create dates and times. Maybe there's a good reason that I'm not thinking of.

Anyway, the attached example shows the undesired behavior when using a "date" that's actually a timestamp, and how the date(floor()) approach solves the problem if someone really doesn't want to change the data model to have a date in it. It also shows how this "date" will have duplicate values in other objects, such as a list box. The example is also there to make sure I've understood the problem you ran across, and that we're not talking right past each other because I've misunderstood.

Hmmm. I really didn't intend to blather on like that.

Anonymous · ‎2011-02-11

I liked your blather. You are right. I did think i was getting a date and time field using the date() and time() functions. I did read (after i discovered the problem) that they are "formatting" functions. Strange to have a formatting function at the script level simply affect data only for display but there it is! You too point out that this may not be ideal.

When thinking about partial sums, I think back to old school reporting tools and setting subtotals. Essentially they operated on the data as it appeared (displayed) in the report.

Say your report has 3 dimensions - Year, Month and Day and then some measures. So, if I want a subtotal under Date (or rather day) then I would expect a total to go at the bottom of Date...but, this would NOT cause a break by Day, it causes a break and subtotals to be generated when Month changes....the dimension level above Day!

I totally get your point and I am not convinced it should be one way or the other at this point. However, i find it unintuitive that the partial sum or subtotal property acts on something different than what you 'see" in your data. Intuitive is very personal though. If I hadn't had a whole bunch of experience with other tools that behaved differently, intuitive would like be exactly how Qlikview functions :).