3

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 other a category - and four measures.

Initial table

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: 

=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'.

Data with newly calculated measure

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 been done, but perhaps this is nonsense.

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

etste
  • 55
  • 1
  • 2
  • 7
  • 1
    I'm not sure what you're trying to achieve but it might require you to review your data model, maybe try a separate question with example data and talk through the goal, there might be a different way to approach this – Chris J Oct 11 '16 at 09:29

2 Answers2

2

If I'm understanding correctly, I believe this solution should work:

  1. Create a variable for your slider: new_measure_slider.
  2. Create a New Sheet Object -> Slider/Calendar Object.
  3. Configure your slider to control your new new_measure_slider variable.
  4. Create a calculated dimension in your chart substituting your 'New Measure' formula (the one you stated was an average of the three ranks). It should be a conditional like this: =if(aggr([your average formula here], Category, UniqueID) >= new_measure_slider, [Category], null()). Basically, compare your formula to the new_measure_slider variable. If true, use the Category (or UniqueID, whichever you need) as the dimension, if false, null().
  5. Check the 'Suppress When Value is Null' checkbox on your new dimension. This is key. This is what will actually filter your chart.
  6. In the chart properties, Presentation tab, click on your new calculated dimension and hit 'Hide Column'. We don't need to see this because we are using it only as a filter.
bdiamante
  • 15,980
  • 6
  • 40
  • 46
  • Sorry, pressed enter before finishing writing! At the end I am using something very similar to what you suggested in step 4, i.e. a conditional statement with my formula, which compares to a variable. In a test sample where I recreated the issue, this solution works just fine. In my actual dataset, I get a weird behavior. If, for instance, my 'new_measure_slider' is set to 80, I would get some rows for which my formula yields 75, 70. Not really sure what is causing this. – etste Oct 12 '16 at 15:50
  • What field are you using as the True parameter in your calculated dimension formula? If you're using Category, it could be that one of the UniqueID's is above the threshold in one of the categories but since Category is the dimension, it's pulling in the ones under the threshold as well. If this is the case, switch that to UniqueID in your dimension formula. – bdiamante Oct 12 '16 at 16:29
  • It seems now to be working fine. I was not aggregating my conditional as in your step 4. Once I changed that, the issue appeared to be solved. Thank you, this has been quite a headache. – etste Oct 13 '16 at 08:46
  • It's a huge headache for what seems like it should be core functionality. It's so cumbersome to set up (especially if you have multiple sliders/dimensions you want to filter by) that every time I think about adding this to a chart or graph I have to allocate half a day or so. At least it's working now and it wasn't all for naught... been there a bunch of times too. Such is life working with Qlik. – bdiamante Oct 13 '16 at 15:23
0

You can tell QV to ignore your filtering in the field Amount by adding "Amount=" to your set analysis.

I dont know how your average calculation looks like but maybe:

(aggr(rank(sum({<Amount=>} Score1)), Category, UniqueID) + 
aggr(rank(sum({<Amount=>} Score2)), Category, UniqueID) + 
aggr(rank(sum({<Amount=>} Score3)), Category, UniqueID)) / 3
mickeger
  • 948
  • 1
  • 6
  • 18
  • Thank you @mickeger. However, I am actually interested in keeping the filtering in the field Amount. – etste Oct 11 '16 at 08:27