Qlik Community

Qlik Sense Cloud Discussions

Highlighted
paulien1989
New Contributor II

Need help constructing an advanced measure

Hi, I need help constructing a measure in Qlik Sense within the model, using email data. I have two tables:

Email Results (containing ID of recipient)

Clicked Links (containing content of link and total clicks on that link, per ID of Email Result)

In the last table, I can sum the total amount of clicks for each email result. But that's not what I want.

I want to add a measure on the Email Result table, somewhere along the lines of:

CALCULATE(SUM('Clicked_Links'[Total Clicks]) ; FILTER( NOT ( CONTAINS ( 'Clicked Links'[Content]; "view as a web page")) ; FILTER ( NOT ( CONTAINS ( 'Clicked Links'[Content]; "privacy policy")))

This would result in a sum of total clicks per row on the Email Results table (and thus per recipient), where only clicks on links are summed that are relevant (so not relevant links should be excluded from the measure).

I achieved this easily in MS Power BI, but I'm at a total loss at how to do this in Qlik Sense. Can anyone help me?

And please, be detailed. Just saying something like 'you should make a variable' does not help me without saying how and where to do that.

1 Solution

Accepted Solutions
paulien1989
New Contributor II

Re: Need help constructing an advanced measure

Hi, I couldn't make it work, it's like it didn't get my filter conditions.

I solved it in another way now. I put a calculated field on the Clicked Links table, indicating if a link should be marked as relevant content with true or false. Based on that, I've created a master measure calculating clicks for only relevant content ('true'), somehow that did work. Thanks for the inspiration!

3 Replies
kingsley101
New Contributor III

Re: Need help constructing an advanced measure

Hi Paulien.

I think you are looking to add a measure on the front end and not in the load script, thus create a new measure with this calculation:

AGGR(SUM(IF([Clicked Links]<>'*view as a web page*' AND [Clicked Links]<>'*privacy policy*',[Clicked Links])), EmailID,[Clicked Links])

To do it in the load:

SUM(IF([Clicked Links]<>'*view as a web page*' AND [Clicked Links]<>'*privacy policy*',[Clicked Links])) as NumberOfClicks


Thanks

Kingsley

paulien1989
New Contributor II

Re: Need help constructing an advanced measure

Hi, I couldn't make it work, it's like it didn't get my filter conditions.

I solved it in another way now. I put a calculated field on the Clicked Links table, indicating if a link should be marked as relevant content with true or false. Based on that, I've created a master measure calculating clicks for only relevant content ('true'), somehow that did work. Thanks for the inspiration!

kingsley101
New Contributor III

Re: Need help constructing an advanced measure

Hi Paulien.


That's great that you got it to work.


This is what I gave to you:

SUM(IF([Clicked Links]<>'*view as a web page*' AND [Clicked Links]<>'*privacy policy*',[Clicked Links])) as NumberOfClicks


And I should have given you this:

IF([Clicked Links]<>'*view as a web page*' AND [Clicked Links]<>'*privacy policy*',[Clicked Links]) as NumberOfClicks


And on the front end, just Count(NumberOfClicks). However, your solution is 100% valid!


Thanks