How to pass current dimension in p() function of Set Analysis?

[perla_chirag]

Hi Qlikers,

I have an expression where I use a p() function of Set Analysis.

=Count(DISTINCT {<CookieID=p({<Brand={'BrandY'}>}),Brand={'BrandX'}>}CookieID)

What I am trying to achieve here is:

- Out of those CookieIDs who have seen BrandY, I want the Count of Distinct CookieIDs who have also seen BrandX.

So the expression above works well for overall data.

When I use it in a chart, where I have Month as a dimension, it should only show CookieIDs who have seen BrandY in that month who have also seen BrandX in that same Month of the Dimension value.

So I have narrowed the problem down to this particular part of the expression, where I need your help:

CookieID=p({<Brand={'BrandY'}>})

Here, the p function returns all CookieIDs that looked at 'BrandY' over the entire time, and not just the Month in the corresponding dimension.

What should I use inside the p() function for it to consider only those CookieIDs that looked at 'BrandY' in that particular month?

Once this part is done, the expression will work as expected.

Thanks, I'm really looking forward to some insights being shared on this

[jonathandienst]

You need an intersection of the sets of 'seen X' and 'seen Y':

=Count({<CookieID=P({<Brand={'BrandY'}>})>*<CookieID=P(Brand={'BrandX'})>} DISTINCT CookieID)

[sunny_talwar]

Jonathan, correct me if I am wrong, but wouldn't this work as well?

=Count({<CookieID=P({<Brand={'BrandY'}>})*P({<Brand={'BrandX'}>})>} DISTINCT CookieID)

also I think you original expression seem to be missing from curly brackets and these -> <>

=Count({<CookieID=P({<Brand={'BrandY'}>})>*<CookieID=P({<Brand={'BrandX'}>})>} DISTINCT CookieID)

[perla_chirag]

Hi Jonathan,

Thanks for the response.

I tried it, but i guess our basic concern is that p() returns overall numbers disregarding the current dimension value For e.g. Month.

What I mean is that P({<Brand={'BrandY'}>}) returns all CookieIDs who have looked at BrandY over the entire lifetime of the data. But we want it to return only those values which correspond to each Month(which is our Dimension in our Chart).

Hope my concern is legible to everyone who is reading this.

[perla_chirag]

Hi Sunny,

Thanks for responding.

In your expression, since there are using 2 p() function instead of the earlier expression's 1 p() function, the number is going more off-target, as now both expressions are returning values over their lifetime instead of it showing corresponding to the month in the chart.

Is there anyway that the p() will adhere to the dimension restriction instead of considering overall?

[swuehl · Accepted solution]

Set analysis is only evaluated once per chart not per dimension line.

I think what you need here is a key field that is distinct for each CookieID and Month:

LOAD

     CookieID,

     Month,

     AutonumberHash256(CookieID, Month) as CookieIDMonthKey,

     ...

Then in your chart with dimension Month:

=Count(DISTINCT {<CookieIDMonthKey =p({<Brand={'BrandX'}>})*p({<Brand={'BrandY'}>}) >} CookieIDMonthKey)

[perla_chirag]

Also, Just a suggestion

Waiting for my every post to be 'Moderated' is kind of discouraging from participating further, since we lose track of our thought process in a forum conversation. I really would like to help and ask and share more.

I know it is to combat spam messages... But it should be more automated.

The conversation gets really stunted when you have to wait for a manual moderation which can take hours. Even a simple reply like 'Ok Thanks' also has to be moderated, then it really discourages new members from participating.

I have tried to answer some questions in the past, but since the responses take hours to be moderated and approved, I have rarely come back to participate more. I know there are so many who want to participate but can't due to the moderation restriction. I would really like this restriction to be loosened so that more and more members can have a healthy conversation by having quicker responses.

Its not a rant, but a simple request, which will eventually help more members share and participate in the Qlik Community

And lastly,

Ok Thanks

[perla_chirag]

Ok Stefan. I'll try that approach and let you know how it goes.

[swuehl]

Chirag Perla wrote:

Also, Just a suggestion

Waiting for my every post to be 'Moderated' is kind of discouraging from participating further, since we lose track of our thought process in a forum conversation. I really would like to help and ask and share more.

I know it is to combat spam messages... But it should be more automated.

The conversation gets really stunted when you have to wait for a manual moderation which can take hours. Even a simple reply like 'Ok Thanks' also has to be moderated, then it really discourages new members from participating.

I have tried to answer some questions in the past, but since the responses take hours to be moderated and approved, I have rarely come back to participate more. I know there are so many who want to participate but can't due to the moderation restriction. I would really like this restriction to be loosened so that more and more members can have a healthy conversation by having quicker responses.

Its not a rant, but a simple request, which will eventually help more members share and participate in the Qlik Community

And lastly,

Ok Thanks

All this is totally valid, but we are not working for Qlik, nor can we change the forum software settings (I am also not involved in the approval process).

It would probably best if you post above into a new thread into the 'Community' place where Sara Leslie et.al. will have a look.

[I would suggest to keep this thread to the original topic in further replies]

Stefan

[perla_chirag]

I know Stefan

And if you look closely, my reply was not in reply to your post, it was in response to my Original Post (you can make that out by the indent of my post) Cheers.