Set Analysis Question - Count IF Problem

[#1]

Hello all,

I am attempting to compare multiple instances of the same dataset from different time periods. The data is structured as in the table below and while it would be possible to modify the table format it wouldn't be practical given the additional qualitative data that I have omitted.

The objective I have is to get what we would consider total actual remediated form the dataset below which would mean anything that we scanned in the previous scan (11/30/2015) that was not detected on the latest scan (12/14/2015) or anything that was detected on the latest scan and labeled as remediated. In the case of the data below I would expect to see a value of 6 listed as Unique ID 4 does not show up in the latest scan and the Unique ID 8 doesn't show up in the previous scan.

Thanks a lot in advance

Date	Unique ID	Status
12/14/2015	2	Remediated
12/14/2015	3	Remediated
12/14/2015	1	Unremediated
12/14/2015	5	Unremediated
12/14/2015	6	Unremediated
12/14/2015	7	Unremediated
12/14/2015	8	Unremediated
11/30/2015	1	Unremediated
11/30/2015	2	Unremediated
11/30/2015	3	Unremediated
11/30/2015	4	Unremediated
11/30/2015	5	Unremediated
11/30/2015	6	Unremediated
11/30/2015	7	Unremediated

[petter]

You state that you need multiple periods but your example shows only two periods. Do you need to compare exactly two periods pairwise but over multiple time periods?

[petter]

I think this might work for you:

Note that if you want to compare correctly you will have to make a selection box where there is always two and only two distinct periods selected. The test data has only two periods so it will work anyway.

I have attached the demo app

[#4]

Petter,

Thanks for this. Unfortunately this only shows the correct figure when you use Unique ID as Dimension. I have other qualitative fields that I was hoping to pivot on (otherwise I would have a pivot with about 400,000 records showing which my users may not be too keen on).

I may have over complicated my original question with the remediated piece in there. The logic I am really looking for is for Qlikview to count the unique IDs that are present on the first date and not present on the second date. As new records are generated on the new date and old records disappear on the new report compared to the old report a simple delta between the two returns an incorrect result.

For a little more background I'm solving this in excel right now by following the steps below:

1.taking the list of remediated, stripping out anything remediated before the earlier date

2. Take the Unique IDs from the earlier dated report and run a Vlookup on them against the later dated report. The later dated list would contain both the remediated added to the unremediated.

3. Count the #N/A values produced on the vlookup

4. Add those values to values taken from step 1 and you have your answer.

Right now these steps need be done on 7 different reports weekly so its a bit of a pain point and I would love to be able to get this reporting into Qlikview.