Count of Dates Not Excluding Zero Counts

[#1]

Hello Qlikview community:

I have a table where I am trying to show items and their associated count of dates. I only want to show items where the count of dates is less than the total amount of dates in the current selections (3 in the example below). My current logic is working fine except for one part--items that have zero dates in the current selection are excluded. I need a way for the count to return zero in this situation.

The problem i'm having is that these items are not in the current selections since they have no associated dates. I can force these extra rows to show up by adding in a new expression Count({$<POSDate=>}POSDate); however, this results in all items showing up regardless of how many dates they have.

This is before adding in Count({$<POSDate=>}POSDate)

This is after adding in Count({$<POSDate=>}POSDate)

All the fields discussed in this problem are part of one table.

What I would like to ultimately like to end up with is the first 7 rows in column 3 (the second picture).

Any ideas on how to resolve this issue?

Thanks,

B. Dykstra

[#2 · Accepted solution]

I solved this by implementing an alternate state for my calendar. This allowed me to view items that were outside of my current date range.

The expression I ultimately ended up with:

Sum(Aggr(RangeSum(

If(Count({$<POSDate=P({[Alt Calendar]})>}POSDate) < $(vStoreItemKeyPOSDateCount) or

IsNull(Count({$<POSDate=P({[Alt Calendar]})>}POSDate)) = -1, 1)),StoreNo,[Item #]))

vStoreItemKeyPOSDateCount:

=Count({$<StoreNo=, [Item #]=,POSDate=P({[Alt Calendar]})>}DISTINCT TOTAL POSDate)

I had to use this variable since getSelectedCount() doesn't seem to work with alternate states.