Intersection, Set analysis, Charts and the P function

[#1]

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 )

[Gysbert_Wassenaar]

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 )

[#3]

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.

[Anonymous]

Hi Fredrik,

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

Regards

[#5]

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)

[qlikslave · Accepted solution]

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

[#7]

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.