Filter values greater than divide

[GabrielOtet]

Hi all,

I have a problem when calculating a specific value as following:

count({<field1={"$(=[field1]*3/[field2])>5"}>} field1)

Actually what I want to do is to count all the values that divided to another value from a collumn of the table are greather than 5. 

Theoreticaly the logic should be correct but practically I get different results compared with what I expect.

can someone help me here?

Gabriel

[sunny_talwar]

Try this

Count({<field1 = {"=[field1]*3/[field2] > 5"}>} field1)

[marcus_sommer]

Try it without the $-sign expansion within the set analysis:

count({<field1={"=[field1]*3/[field2]>5"}>} field1)

- Marcus

[GabrielOtet]

Ufortunately it is not working as expected, it is returning a value but is far away from the expected one.

Most probably behind it is a logic that I cannot get.

I have tried with IF also but there is no result.

[sunny_talwar]

Would you be able to share a sample where we can try this out?

[GabrielOtet]

I cannot do it but I'll try to explain as best as possible:

I have a ColumnA and a ColumnB into the data table (there are many more but those two are important for my issue).

Now, I have to calculate the amount of rows that have the property as ColumnA/ColumnB is greater than a Value.

The filter is working fine until to the point that I divide the values (with a single value comparison for example) but not working anymore with the full filter.

As a solution I'm thinking that maybe will be faster to build up a third ColumnC with the result of ColumnA/ColumnB when loading the data. In this case, in filter will be only ColumnC which have the values greather than something.

Anyhow, I have the feeling that there is a more elegant solution directly with filters.

[sunny_talwar]

If all you want to do is to divide a column with another... why are we multiplying with 3 here?

Count({<field1 = {"=[field1]*3/[field2] > 5"}>} field1)

Is this what might be causing the issue?

Again, not asking for confidential data... all I want to see is a mocked up sample data which is close to your real data and where we can see the issue and see what you might be doing wrong.

[GabrielOtet]

My bad, into the explanation I forgot to mention about the '*3'.

Into the real view, there is this part also.

[marcus_sommer]

It seems that the calculation logic and/or the field-associations within the datamodel and/or the data-quality isn't really clear. Therefore I suggest to create a table-chart with field1 as dimension and field1 and field2 as expression and also your calculation of [field1]*3/[field2]>5. I wouldn't really surprised if it don't work. I assume that your logic need some aggregations ...

- Marcus

[GabrielOtet]

Hi Marcus,

It is working with the observation that out of approx 100k values of field 1, some of them are equal and will not show the real amount of samples/values.

Other than that, using the table you suggested to check/see the data is working perfectly.

Also additional field with value  [field1]*3/[field2]>5 is showing the right results.

On the other hand, having in data Load an additional column is working straight forward and giving the right results but as I said, I'm still thinking that there is a more elegant solution with filtering data directly.

Gabriel