topic Re: What's wrong with using formula on referenced expressions? in QlikView

What's wrong with using formula on referenced expressions?

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Can anyone guide me as what's wrong with my expression calculations in following straight table.

Table 1

Table 2

Here's what's happening,

I can create some expression based on a metric say Revenue, e.g. sum(Revenue) in Table 1 and max(Revenue) in Table 2.
Next, I am able to successfully get results with basic operations by referencing this new expression (as [expression] or using labels). For example, [sum(Revenue)] + 2 in Table 1. All good so far
Problems start as soon as I apply any formula to this newly calculated expression and everything goes haywire? As you can see in column 4 of Table 1 and in column 3 of Table 2 above that results are not as expected. The results are wrong no matter what function I apply to the referenced expressions.
My confusion is that why it's not allowed to apply any formulas to expressions created in charts. Or am i doing something wrong here?

Best Regards,

Ehsan

Re: What's wrong with using formula on referenced expressions?

MK9885 — Wed, 29 Aug 2018 16:17:23 GMT

Sum(TOTAL(Revenue))

Not sure about 2nd example you looking for...

What you like that max function to do?

Revenue should be max of revenue

Re: What's wrong with using formula on referenced expressions?

chrismarlow — Wed, 29 Aug 2018 16:35:10 GMT

I don't think you are doing anything wrong, just that using the column names like that only works for simple operations (I am happy to be contradicted though). So I would use that to avoid having multiple copies of complex formulae where I then have a difference column (so then you only have to amend the formula in the original column, rather than 2 or more places).

When I look the error message on the formula you have says bad field name - suggests for those more complex calculations it wants to work with actual fields rather than columns you have created.

Regards,

Chris.

Re: What's wrong with using formula on referenced expressions?

Anonymous — Wed, 29 Aug 2018 16:41:33 GMT

I totally agree and I actually don't refer columns like this.

It's just I am using this [ ] referencing here to better explain my query

Re: What's wrong with using formula on referenced expressions?

tulabandula — Wed, 29 Aug 2018 16:45:26 GMT

-->

=Sum(total(field)) //Sum may not require After total qualifier i guess

-->

There is already an expression as Max(Rev) so =Rev>=(max(rev)) is going to be equal i think.

Re: What's wrong with using formula on referenced expressions?

marcus_sommer — Thu, 30 Aug 2018 07:42:41 GMT

In general you could within a single object refer to another expression by the expression-label or the column(INDEX) - whereby you should rather avoid that the labels are named like existing fields or expressions (it may work in many cases but it could be confusing - for Qlik and yourself - and makes a possible trouble-shooting more difficult). Beside this I'm not sure that you have a label for your column 4 because displaying the expression within the label doesn't mean that a label is existing.

Further you need to add a total-statement within the aggregation if they should ignore all or certain dimension-values like: max(TOTAL Revenue).

For your column 3 you need to add an aggr() if you want that your total is the sum of your rows. This means something like: sum(aggr(sum(Revenue)+2), CompanyName)).

- Marcus