topic Re: Really odd behavior in QlikView

Really odd behavior

giakoum — Thu, 06 Sep 2012 15:19:52 GMT

Hi all.

I have an expression which calculates days depending if the previous 3 weeks are there in the data. For example, if 2012W23 to 2012W27 is there for every seller, the result is 28 days for every week, if there is one week missing, i.e. 2012W25 the result is 21 days, if there are 2 weeks missing i.e. 2012W23 and 2012W25, the result is 14 days, if all 3 previous weeks are missing, the result is 7 days.

This seems to be working well in a straight table. I take the average of rows as a total, and the result is quite fine. it does not matter which weeks are missing, the result is as expected.

But I need to present this average of rows as one number in a text box, so the obvious choice was to aggr the expression over seller and week, in order to mimic the chart. See attached file.

If all weeks are present, the aggr gives the same result as the chart.

If week 2012W12 for seller a is omitted, the aggr gives the same result as the chart.

If week 2012W09 for seller a is omitted and seller b has data up to week 2012W10, the aggr gives the same result as the chart.

If week 2012W09 for seller a is omitted and seller b has weeks present over week 2012W10 for example 2012W11, the result is different.

You can try these scnarios in the attached file.

I only want to aggr the values the chart is showing, I cannot understand why it is related to data. The chart is working, the aggr is not, depending on the data.

Any ideas?

Re: Really odd behavior

giakoum — Thu, 06 Sep 2012 15:53:18 GMT

I created a list box with the text box expression to see where the difference comes from. It seems that when a week, for example 2012W09 is missing from Seller a, seller b has wrong results from 2012W09 and onwards. I cannot understand why this happens in the aggr only and not the chart itself :

Re: Really odd behavior

whiteline — Thu, 06 Sep 2012 16:52:05 GMT

I guess, the problem is with your expression not with aggr.

You use inter record functions with aggr.

Why do you expect that tha data representation and sorting for (inside) aggr is the same as in your chart ?

Re: Really odd behavior

whiteline — Thu, 06 Sep 2012 16:54:42 GMT

Try 'load order' as sort order and you'll see the difference.

Re: Really odd behavior

giakoum — Thu, 06 Sep 2012 17:12:58 GMT

True but that is what aggr function is doing. It is ordering the same way, as you can see from the comparison between the chart and the list box.

How would you solve this?

Re: Really odd behavior

whiteline — Thu, 06 Sep 2012 17:48:39 GMT

If it's order by the same way - you get the same result

I guess, I add some fields so that I can avoid interrecord functions.

Whatever, your expression does not imply user selections.

Re: Really odd behavior

giakoum — Thu, 06 Sep 2012 18:35:13 GMT

Well, I was first pissed with whitelines's answer, but then I thought I look into it a little. And yes, whiteline was right, aggr takes load order by default, it always does, and since 2012W09 was missing from the first seller the order was as follows :

....

2012W08

2012W09

2012W11

2012W12

2012W10 (since this week was only found for seller b!!)

So I simply loaded the weeks seperately in the desired order and now everything works fine! See attached file. A simple list box with weeks ordered by load revealed the schocking, but to be expected truth!

Sorry whiteline for all the nasty thoughts I made , you were really helpfull!

Thank you.

Re: Really odd behavior

whiteline — Fri, 07 Sep 2012 05:42:17 GMT

It's ok. Never mind. I think its normal to be in doubt about comunity comments.

At least you've tried it and I've found something useful too.