Formula is not displayed correctly in the table

[Daniel1908]

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

[marcus_sommer]

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

[Daniel1908]

Hi Marcus, 

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

Kind Regards,
Daniel

[marcus_sommer]

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

[Daniel1908]

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

[Daniel1908]

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

But the formula works. 

Any Ideas? 

Kind Regards, 
Daniel

[marcus_sommer]

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

[marcus_sommer]

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