How to do a calculated dimension based on a measure

[mplautz]

So I will start by being clear that I know that QlikSense ≠ Microsoft Excel.

BUT

I was wondering how to recreate some of this functionality on a table within a Sheet inside of a Qlik app. Basically, I would like to have a dimension on a table that is based on a measure earlier on in the table. 

For simplicity's sake, consider this table, with the following columns:

Column Name Column Type

Geo	Dimension
Account	Dimension
Sales Rep	Dimension
QTD Revenue	Measure
% of Target	Measure

Let's say now that I add a sixth column, called "Halfway", which is a dimension. It is based on the fifth column (i.e. the second measure), and would have a calculation like this:

=if(column(2) >= .5, 'Yes', 'No')

Then, I could set a filter on this "Halfway" dimension for all those where "Halfway" = "Yes", as well as likewise where it equals "No".  This is something I can do with ease in Excel.

Well if I try to do this, it is as if column(2) is null.

Now, I understand the inherent problem with this, and that is that all measures are based on dimensions, and how fine a measure breaks down depends on the dimensions surrounding it. So, maybe what I want to do is not possible. But that's not good enough, and I don't like that.

Well, what if you just make it a measure instead? Well I could. I could rewrite column six as a measure where it is either 1 or 0 based on whether or not it is halfway, and I would use a formula like this:

=round(column(2))

That would work brilliantly. The only problem is Now I can't filter on it! 

Is there anything, even an extension that anyone knows of, or maybe a totally different way of approaching this, that allows me to have a filterable dimension field based on a measure?

[robert99 · Accepted solution]

Hi Mplautz

Use Aggr in a dimension

aggr (                                                   //start aggr

if (measure >= 0.5,'Yes','No' )  //if statement

,Geo,Account,SalesRep)          //one or more fields to aggr by

Then you can filter by the Yes No results

[mplautz]

Hi @robert99 , 

This solution is brilliant. I'm so glad it was this simple. There are two comments I have that perhaps you may be able to address:

1. In your example, you reference measure. If I try to put column(2) in place of measure, it does not appear to work. Still not even if I put num(column(2)). By "it does not work", I mean I get the same result as not having aggr at all – it is as if measure is just equating to null. However, if I put the full formula that calculated measure in its place, then it works. In this case, I'd have to substitute the entire formula that comprised the % of Target measure. Do you know if I should be able to use the "column" function? Or is this something that is not supported? If not, I can work through it.
2. When I use your example, I am only getting "Yes" values, and all "No" values are displaying as a hyphen (-), or what I understand to be null. If I test for measure to be null by wrapping an if(isnull(measure), "N", ...) around the "if statement" portion of the formula, I still get the hypen (-) in my data. Ultimately, the field will be binary, either "Y" or "N", and technically "Y" or "-" is still binary, but this will be problematic, as I do not know of a way to select only "null" values. Is there something I need to do differently to show cases where measure is equal to 0 so it does not show as nulls? Or perhaps another question, is there a way to select only null values in a dimension?

[robert99]

@mplautz 

It should give you both Yes and No. Have you played around with the aggr dimension. For example change the order. Or add more or less dimensions.  I try to avoid using aggr but at times I have no choice. But it can be tricky to get it right. So you might need to use

aggr (                                                   //start aggr

if (measure >= 0.5,'Yes','No' )  //if statement

,Geo) 

or

Use Aggr in a dimension

aggr (                                                   //start aggr

if (measure >= 0.5,'Yes','No' )  //if statement

,Geo,Account,SubAccount, SalesRep)   //usually it corresponds to the table used. So if you have 5 dimensions in the table use the same 5 dimensions. But not always I think (or maybe not in this situation - I rarely use aggr in dimensions)

aggr is a separate calculation that is done to produce a sub-table (rather than setting up the table in script. If possible this is my preferred option). That is then used in the measure. But it can also be used to produce a filter dimension field.

[robert99]

Still not even if I put num(column(2)). By "it does not work",

I started using column(2) etc but stopped. I now just copy and paste the measure.

But another option is to use variables and I believe Qlik is happy with this option for now. But they have master measures and hopefully one day they will allow them to be used in dimensions (using aggr), colouring, other measures etc as if they were set up in script. But this might be a big change. But see how using a variable goes

Re nulls. There are a number of articles on this (Henric has a good one). But I tend to use mapping load if possible. I really dislike being unable to filter on nulls.

https://community.qlik.com/t5/Qlik-Design-Blog/Finding-NULL/ba-p/1474279