topic Re: Filtering by a calculated measure involving multiple fields in QlikView

Filtering by a calculated measure involving multiple fields

Fri, 07 Oct 2016 12:11:52 GMT

Hi,

I am trying to use a calculated measure as a way to filter my data, but it's looking more difficult than expected. Let me explain through an example.

I have data of the following type, with two dimensions - one is a unique ID, the a category - and four measures.

My first step is to rank each element by its score, where the ranking is evaluated within the same category. I therefore create a new measure (found thanks to the help of another community member, see this question😞

=aggr(rank(sum(Score1)), Category, UniqueID)

I do this for all three scores, resulting in three new calculated measures. My final calculated measure is the average of the three rankings. Below the example, the calculated measure of interest is the one in bold. Note that in my real world calculation I directly evaluate 'New Measure', without creating the intermediate columns 'RankingScore'.

Note that this measure is tricky, as it changes according to previous selections. Say, for instance, that I select only entries with 'Amount' > 1000. The relative rankings will change and therefore also 'New Measure':

In my actual App I need to filter my entries by 'New Measure', after I've done some previous selections on fields like 'Amount'. If it simply were a field, I would normally have created a filter pane, our used the qsVariable extension to have a slide range, to select only rows with 'New Measure' above a set threshold. Unfortunately it seems I cannot do that with my calculated measure.

How would you approach the problem? I was wondering, for example, if it were possible to 'convert' my new measure to an actual field, after all previous selections have done, but perhaps this is nonsense.

Thank you in advance, and apologies for the long post!

Re: Filtering by a calculated measure involving multiple fields

sunny_talwar — Fri, 07 Oct 2016 16:26:28 GMT

First is this QlikView or Qlik Sense?

Second, would you be able to provide a sample to work with

Re: Filtering by a calculated measure involving multiple fields

Fri, 07 Oct 2016 16:30:33 GMT

Hi Sunny,

I am working on Qlik Sense Desktop

By sample you mean a raw table (say in csv or excel), or a Qlik work file?

Re: Filtering by a calculated measure involving multiple fields

sunny_talwar — Fri, 07 Oct 2016 16:42:52 GMT

A qvf file would be better because then we can look straight into the issue, else we will have to recreate the whole issue before we can offer any help

Re: Filtering by a calculated measure involving multiple fields

Fri, 07 Oct 2016 17:23:56 GMT

I created a sample work replicating the issue, you can download it from here.

Note that I am using two extensions, qsVariable and Sheet Navigation + Actions.

Essentially, I create sliders to filter my data according to fields 'Amount1' and 'Amount2'. I want to be able to do the same with my calculated measure 'Score_Avg', which embeds category-relative rankings as I described above.

I hope the files works and is clear enough!

Re: Filtering by a calculated measure involving multiple fields

Tue, 11 Oct 2016 14:45:47 GMT

Anyone who can give me their opinion?

I would much appreciate it.

Re: Filtering by a calculated measure involving multiple fields

sunny_talwar — Tue, 11 Oct 2016 16:03:21 GMT

Apologizes as I won't be able to help as I don't have those extensions and I don't mind downloading them, but I have very limited knowledge on how to use them. I hope someone with more expertise in Qlik Sense might help you better here

Best,

Sunny

Re: Filtering by a calculated measure involving multiple fields

Tue, 18 Oct 2016 15:24:58 GMT

Found a solution with the assistance of another user in Stack Overflow. Just wanted to share it here with the rest of the Qlik community.

Re: Filtering by a calculated measure involving multiple fields

odassier — Fri, 06 Jan 2017 09:29:28 GMT

Hi Stefano, would you mind uploading the same qvf file you posted previously but wit the solution implemented? that would help me understand the workaround you are referring to.

thanks,

Olivier