topic Conflicting Summary Data - At Wits End in QlikView

Conflicting Summary Data - At Wits End

Mon, 23 May 2011 08:46:52 GMT

All,

I am not sure if I have been too focussed on the details and now cannot see the obvious, but clearly I am missing something obvious. I have reduced my dataset to 2 days and attached the .qvw file to illustrate my problem. But please reply in the post as well as posting a solution .qvw as I am using a single user license and cannot open posted worksheets. License due to be signed off shortly.

The files are read in as follows:

In the Inbound Transactions tab, I have created a couple of tables - it looks messy as I am trying to debug it . . . but one of the selectors is essentially the transaction type: IMP / EXP / ADJ- / ADJ+ / TRFI / TRFO.

Now I create several table all with largely the same columns - as I only want to let the user see Inbound transactions on this tab, I use the following expression when summing the total volume: =If([TransDet TXN_TYPE]='IMP',SUM([TransDet TOTAL_VOL])). Yet I get different results from a chart using this expression as I do if I use the selector to select the transaction type and my expression is SUM([TransDet TOTAL_VOL]).

Not only do I get different totals, but I also have a different number of principals displayed and summed over. I am completely stuck and I am sure it is something very obvious! I have tried to make the transaction table extremely simple and straight forward to avoid any confusion, but I do link to a principal fact table and a date table to generate some additional information, but they are very simple sttraight forward keys.

Another problem I am seeing is if I for instance add in an additional dimension, such as SITE_CODE, into one of the tables, suddenly the number of principles is increased? How is this possible? Why?

I also see that I get inconsistent results when enabling the partial sums checkbox - sometimes it displays, other times it may only display at random rows in the pivot chart.

I know someone out there is going to be able to help me - and please explain the answer as opposed to just supplying the worksheet as I cannot yet read others worksheets.

Thanks

Ian

Conflicting Summary Data - At Wits End

Mon, 23 May 2011 09:09:44 GMT

When I load the dates, I load them as Date(Floor(TXN_DATE)) and when I use the TXN_DATE as a dimension, I do it as a calculated dimension using (Date(Num(Floor(TXN_DATE). I use the same expression for my selector field. However, when exporting to Excel, I can see that the underlying fractional part is still embedded. I am sure that this is affecting the results as if the underlying data is a timestamp as opposed to a pure date (integer only) I will be selecting based on time as well, which I do not want to do.

Is it possible to drop the fractional part as part of the SQL import into QV? I will never use the timestamp component. If not is there a way to discard the fractional portion within QV? I would have thought by using the (Date(Num(Floor(TXN_DATE) expression I would at least simulate this.

I think I need to get rid of this annoyance prior to trying to solve the other issues which will undoubtably sit ontop of this mess?

Looking forward to a quick and sharp learning curve.

Thanks

Ian

Conflicting Summary Data - At Wits End

Mon, 23 May 2011 09:25:49 GMT

Try this Instead :

=SUM(IF([TransDet TXN_TYPE]='IMP',[TransDet TOTAL_VOL]))

Conflicting Summary Data - At Wits End

Mon, 23 May 2011 10:30:28 GMT

Nice,

And now that you mention it, I recall reading something along the same lines on the old community forum. Can anyone briefly elucidate on the internal logic of why this makes a difference?

Secondly, any suggestions on the date issue and dropping the timestamp?

Thirdly any suggestions on the partial sums?

Thanks

Ian

Re: Conflicting Summary Data - At Wits End

Mon, 23 May 2011 10:49:42 GMT

When we use this:

SUM(IF([TransDet TXN_TYPE]='IMP',[TransDet TOTAL_VOL]))

Then it first calculates the Sum and then applies IF Condition and classify data according to the IF Condition.

In this case:

If([TransDet TXN_TYPE]='IMP',SUM([TransDet TOTAL_VOL]))

It calculates Sum only when we select IMP.It means Sum is calculated only after IF condition is fulfilled.

That is why in this condition Show partial sums does not show any value.

Try This For date issue:

Date(DateField,'DD/MM/YYYY')

this will avoid Timestamps.

Conflicting Summary Data - At Wits End

Mon, 23 May 2011 11:55:50 GMT

Wahoo, I think I got you Erika. Now all I need to do is work out out to import only the interger portion of the dates? Anyone out there inclined to comment?

Thanks

Ian

Conflicting Summary Data - At Wits End

Sun, 29 May 2011 12:14:30 GMT

I eventually solved the date issue by reading in the date using the floor() function. This was used during the load statement and only passed the integer portion of the date through to the named field - effectively dropping the timestamp.