Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis Question - Count IF Problem

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

   

   

DateUnique IDStatus
12/14/20152Remediated
12/14/20153Remediated
12/14/20151Unremediated
12/14/20155Unremediated
12/14/20156Unremediated
12/14/20157Unremediated
12/14/20158Unremediated
11/30/20151Unremediated
11/30/20152Unremediated
11/30/20153Unremediated
11/30/20154Unremediated
11/30/20155Unremediated
11/30/20156Unremediated
11/30/20157Unremediated

3 Replies
petter
MVP
MVP

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
MVP
MVP

I think this might work for you:

2016-01-05 #1.PNG

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

Not applicable
Author

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.