topic Missing records In Straight table in QlikView

Missing records In Straight table

Tue, 31 Jul 2012 12:26:45 GMT

Hello:

I am having an issue with one of the tables where only a certain amount of my records are being displayed. It always cuts off at different points depending on the dates you have selected. The table compares items on a date-to-date selection based on the user's input.

Item # Items Gained Items Lost Net Change

78588 830 0 830

47699 815 0 815

236100 83 0 83

89508 - - -14

06355 - - -11

85819 - - 7

Now when I add in store #, these numbers come out fine, except split into single counts. The problem is I need the counts as shown above. Additionally, the net change shown is correct, and also the missing records happen with both poisitive and negative net change values.

My dimension here is item #. The expressions are as follows:

Items Gained: If($(vNewestPOSDateItem) <> $(vOldestPOSDateItem),

$(vNewestPOSDateItemCount))

Items Lost: If($(vOldestPOSDateItem) <> $(vNewestPOSDateItem),

$(vOldestPOSDateitemCount)

Net Change: $(vNewestPOSDateItemCount) - $(vOldestPOSDateItemCount)

vNewestPOSDateItem: Only({$<POSDate={'$(=(vNewestPOSDate))'}>}Item)

vNewestPOSDateItemCount: Count({$<POSDate={'$(=(vNewestPOSDate))'}>}Item)

vOldestPOSDateItem: Only({$<POSDate={'$(=(vOldestPOSDate))'}>}Item)

vOldestPOSDateItemCount: Count({$<POSDate={'$(=(vOldestPOSDate))'}>}Item)

Any idea of what I'm doing wrong here?

Re: Missing records In Straight table

jonathandienst — Tue, 31 Jul 2012 12:40:17 GMT

Not sure about this, but I suspect that some of your only expressions match more than one item, and therefore return null. Only() returns a value when there is only one possible value, otherwise it returns null.

Regards

Jonathan

Re: Missing records In Straight table

Tue, 31 Jul 2012 12:59:15 GMT

Hi Jonathan:

I don't think this is the case, as I have drilled down into the data to make sure.

Take a look at the attached screen shot. Store # 1864.

Re: Missing records In Straight table

jonathandienst — Wed, 01 Aug 2012 09:05:45 GMT

I don't fully understand the logic in your model. Perhaps you could post your model or a sample of it.

It is clear to me, though, that the only() statements are failing, that's why net change works, but not the other two expressions.

Regards

Jonathan

Re: Missing records In Straight table

Thu, 02 Aug 2012 13:34:36 GMT

Hello Jonathan:

Unfortunately, I can't post anything other than screen shots.

Any ideas of how to evaluate this problem differently?

I'm basically looking for a way to say show me the items that are here today and were not here yesterday, and also, show me the items that were here yesterday and not here today.

The net change gives an accurate number, but it doesn't show what actually happened.

Thanks,

Ben D.