Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Intersection, Set analysis, Charts and the P function

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 )

1 Solution

Accepted Solutions
qlikslave
Contributor
Contributor

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

View solution in original post

6 Replies
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 )


talk is cheap, supply exceeds demand
Not applicable
Author

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
Not applicable
Author

Hi Fredrik,

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

Regards

Not applicable
Author

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

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

Not applicable
Author

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.