topic Re: How to pass current dimension in p() function of Set Analysis? in QlikView

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

perla_chirag — Thu, 14 Apr 2016 08:15:59 GMT

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

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

jonathandienst — Thu, 14 Apr 2016 08:46:24 GMT

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

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

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

sunny_talwar — Thu, 14 Apr 2016 09:38:42 GMT

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)

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

perla_chirag — Thu, 14 Apr 2016 09:39:56 GMT

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.

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

perla_chirag — Thu, 14 Apr 2016 09:45:49 GMT

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?

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

swuehl — Thu, 14 Apr 2016 09:59:55 GMT

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)

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

perla_chirag — Thu, 14 Apr 2016 10:05:14 GMT

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

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

perla_chirag — Thu, 14 Apr 2016 10:07:56 GMT

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

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

swuehl — Thu, 14 Apr 2016 10:51:32 GMT

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

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

perla_chirag — Thu, 14 Apr 2016 11:08:54 GMT

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.

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

swuehl — Thu, 14 Apr 2016 11:28:39 GMT

Ok, going off-topic

I am by default using a chronologic view of the posts,not the hierarchic view, so I apologize if I am answering questions I've not been asked (or since you haven't really asked something, just comment on everything)

Still, your points are valid and I would suggest that you repost to Community place.

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

perla_chirag — Thu, 14 Apr 2016 11:35:38 GMT

Thanks Stefan, nothing to apologize about, I now understand that it was a misunderstanding due to the forum view.

Will post about the moderation in the Community place

Coming back to our Thread Discussion, I've put your answer into my application and am running it, will let you the results. Thanks a lot for your help!

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

perla_chirag — Thu, 14 Apr 2016 13:40:08 GMT

Stefaaaaan! It worked like a charm!!!

This will save us a lot of time when looking at data on a User-level : Month-level.

Thanks for your help