Analysis Level Calc does not work if at dataset level

Question & Answer
, , ,
1

I have an analysis that does not work when at the dataset level, but works at the analysis level. This calculation works fine in other datasets and we’ve used it for a while, but does not seem to work in the version I’m working with at the dataset level.

The Data preview while editing the dataset shows the calculation working just fine, but when trying to use it in a visual, it will sum to 0. Data type is numeric and there are no null values present.

The calculation: ifelse({volume_unit} = “ml”, {order_volume}/1000,{volume_unit} = “l”, {order_volume},{volume_unit} = “us_g”, {order_volume}*3.785,{volume_unit} = “us_oz”
,{order_volume}*0.02957353,0)/9

2

Hi @matthew.fahy - Can you please share sample data where the analysis is giving 0 result. The formula looks fine, we need to validate the data.

Regards - Sanjeeb

1 Like
3

image
image1256×689 35 KB

Here is some sample data.

4

Any thoughts with the sample data provided? Thank you.

5

Hi @matthew.fahy
could you try a simple calculated field
ifelse({volume_unit} = “ml”,1,0)
And check you are getting a 1 on the “ml” lines?
BR

6

No issues with the test calc.

image
image991×181 22.3 KB

7

Then I would add step by step the next part into the calculation and see if it still works.

8

Hello @matthew.fahy !

Were @Sanjeeb2022 and @ErikG 's suggestions helpful, and if so could you mark their comment as a solution to help the community?

It has been a few days since we have heard from you but would still like to help you find a solution!

9

Neither solution has worked. I’ve added step calculations, and it still drops off at a certain point. Attempting to revise that calc has proved tough. No approaches are working at that step. We’ve opened a support ticket.

This test calc works fine, no issues.
ifelse({volume_unit} = “ml”, 1,{volume_unit} = “l”, 2,{volume_unit} = “us_g”, 3,{volume_unit} = “us_oz”,4,0)

This one still doesn’t work (along with several iterations of it)
ifelse({volume_unit} = “ml”, {order_volume}/1000,{volume_unit} = “l”, {order_volume},{volume_unit} = “us_g”, {order_volume}*3.785,{volume_unit} = “us_oz”,{order_volume}*0.02957353,0)/9

10

Hello @matthew.fahy !

To me this looks like order_volume may have an issue whether that is data type or something else. For example.

If you use @ErikG suggestion above and include order volume does the calculation work?:

ifelse(
{volume_unit} = 'ml', 
{order_volume}/1000,
0)
/9
11

Hello @matthew.fahy !

Were you able to try the calculation I suggested above?

12

I was able to. It still results in 0.

13

So to get it right. @duncan calculation works but it results in 0?
Is it possible that there are spaces in the volume_unit field ("ml " instead of “ml”)?

1 Like
14

Hey @matthew.fahy !

I agree with @ErikG, if that comes back as 0 that tells me that the field isn’t recognizing the value as written which would mean the values currently in your field don’t have the same string/character length.

15

Hello @matthew.fahy !

Were you able to try the suggestion above?

It has been some time since we have heard from you but would still like to help you find a solution. If we do not hear from you in the next 3 business days this topic will be archived.

16

I was able to try the suggestions above. All suggestions reach the same result of null. We have a ticket open with AWS and they in turn created an internal ticket. It seems it’s a very particular issue going on, but not with the calculation itself.