topic Formula is not displayed correctly in the table in App Development

Formula is not displayed correctly in the table

Daniel1908 — Fri, 09 Apr 2021 10:41:04 GMT

Hello Together,

Formula shows OK but in the Table it shows the Prices which are equal too.
I excluded the 0 values as well but they are in the table as well.

=Count (distinct(If([Price-2]>[Price-1],MaterialNumber)))

Any Ideas?

Kind Regards,
Daniel

Re: Formula is not displayed correctly in the table

marcus_sommer — Fri, 09 Apr 2021 13:05:10 GMT

You couldn't compare field-values in this way because the visible values mustn't be exactly the same as the stored values and also Qlik used a binary number-system and not a decimal-system which hasn't for all values an exact number.

This means you need to apply an appropriate rounding-function or changing the comparison-method, for example with:

If(round([Price-2], 0.01)>round([Price-1], 0.01), Count (distinct MaterialNumber))

or maybe

If([Price-2] - [Price-1] > 0, Count (distinct MaterialNumber))

- Marcus

Re: Formula is not displayed correctly in the table

Daniel1908 — Fri, 09 Apr 2021 13:27:32 GMT

Hi Marcus,

unfortunately this did not work, do you have any further ideas?

Kind Regards,
Daniel

Re: Formula is not displayed correctly in the table

marcus_sommer — Fri, 09 Apr 2021 16:13:05 GMT

In this case there might be an issue with your datamodel and/or your data are different to your expectation. I suggest to create a tablebox or maybe a straight-table (has the benefit that you could add further expressions like isnum(Field) or len(Field) or similar checks) and adding there all relevant fields - from this expression and from the used dimension of your chart and those which may be use for selections.

Also useful is often to add an unique key to this table - if none exists you may create one with recno() or rowno() within the script - because each Qlik object will only show the distinct combination of fieldvalues and without such key you want be able to detect any duplicates.

And then you could check your data.

- Marcus

Re: Formula is not displayed correctly in the table

Daniel1908 — Mon, 12 Apr 2021 13:57:51 GMT

Hi Marcus,

now it worked.

If(round([Price-2], 0.01)>round([Price-1], 0.01), Count (distinct MaterialNumber))

Do you have an Idea how to exclude if Price-1 = 00,0 or exclude if status Request = "X"

Additionally to exclude the differnce of the prices at the decimal places.

Kind Regards
Daniel

Re: Formula is not displayed correctly in the table

Daniel1908 — Mon, 12 Apr 2021 14:35:28 GMT

Hi I recognized as well that the total value does not appear in the table.

But the formula works.

Any Ideas?

Kind Regards,
Daniel

Re: Formula is not displayed correctly in the table

marcus_sommer — Mon, 12 Apr 2021 15:40:26 GMT

Probably you could add the further conditions in a way like this:

If(round([Price-2], 0.01)>round([Price-1], 0.01) and
sign(round([Price-1])) and
Request <> 'X' ,
Count (distinct MaterialNumber))

- Marcus

Re: Formula is not displayed correctly in the table

marcus_sommer — Mon, 12 Apr 2021 15:46:24 GMT

It's quite normal that it couldn't work within the TOTAL because there didn't exists an appropriate dimensional context for the calculation - in your case quite probably that there are multiple price-values available which results in NULL by accessing them without an aggregation.

This means you need to wrap your expression with an aggr() like:

sum(aggr(YourExpression, Dim1, Dim2))

whereby Dim1 and Dim2 are placeholder for the dimensional context - usually it are the dimensions of your chart and sometimes also from a certain selection.

- Marcus