topic Re: Intersection, Set analysis, Charts and the P function in QlikView

Intersection, Set analysis, Charts and the P function

Thu, 22 Oct 2015 13:41:50 GMT

Hi Community,

I am struggling to create an expression, which will give me the number of ID:s, found both expressions, grouped by a date. However, when clicking on a certain date, I will get another number than I had when I am looking all the data. I suppose this has something to do with the date, where I need to add aggregate somehow? I should look for ID:s where the CODE:s are found in both cases. Dimensions then shown in a chart is a date on a line chart.

count (distinct {<ID = P ({<CODE={'A', 'F', 'I', 'P', 'O', 'H', 'W', ''I'}>} ID ) >*<ID = P ({<CODE={'M'}>} ID) > } ID )

Re: Intersection, Set analysis, Charts and the P function

Gysbert_Wassenaar — Fri, 23 Oct 2015 05:10:56 GMT

Perhaps you need to override the date selection

count (distinct {<ID = P ({<MyDate=, CODE={'A', 'F', 'I', 'P', 'O', 'H', 'W', ''I'}>} ID ) >*<ID = P ({<MyDate=, CODE={'M'}>} ID) > } ID )

Re: Intersection, Set analysis, Charts and the P function

Fri, 23 Oct 2015 07:10:21 GMT

No, it doesn't work

If the code exists somewhere else in time, it will be included, even that's missing for the corressponding date. Strange.

Re: Intersection, Set analysis, Charts and the P function

Anonymous — Fri, 23 Oct 2015 09:48:41 GMT

Hi Fredrik,

you could use alternate state in your expression to solve your issue.

Regards

Re: Intersection, Set analysis, Charts and the P function

Fri, 23 Oct 2015 13:51:52 GMT

I tried that as well, but when I click on a certain date, I get one number (correct) and when I unselect everything I am getting another number (wrong, too many records)

Re: Intersection, Set analysis, Charts and the P function

qlikslave — Fri, 23 Oct 2015 15:53:11 GMT

My friend, your script will be work if you do:

Load

Id & Date as Id*

.......

Alternative maybe like:

Sum(
Aggr(
IF(

Count({<CODE={'A', 'F', 'I', 'P', 'O', 'H', 'W', ''I', 'M'}>} DISTINCT CODE)=2,

                        1, 0
                    )
                ,Id,Date)
      )

Plz example - intersection of two sets in one field aggregated by date

Re: Intersection, Set analysis, Charts and the P function

Mon, 26 Oct 2015 08:05:01 GMT

Maxim,

Thanks for your answer.

What I did in my script, was to make sure that the ID was unique for the date dimension. That's solved the problem, which you pointed out, but still I am bit uncertain why I need to do that. In a ordinary set analysis expression, I will not seen this happend before.