I am trying to compare two different fields in a calculated field, but it is changing the actual number instead of comparing. Ex:
ifelse({usd_value}>={2X},‘Yes’,‘No’)
I want to compare a value against a max threshold and either return a ‘Yes’ if it is over the threshold or no if it is less. But QS changes the usd_value, which throws the metric off and makes it invalid. Can anyone help?
Do you mean QuickSight updates the value of column “usd_value”? instead of showing Yes or No in the new calculated field?
can share a screenshot?
It does both, it shows Yes or No, but changes the usd_value. When you remove the calculated field the usd_value returns to what it is supposed to be. I’ll work to include a screen shot.
I am trying to simulate your scenario by using below sample data. And created 2 tables, one of them added column “2X Threshold” The total value are the same. May i know if you have applied any filter on the table?
If you did it in 2 separate tables how were you able to combine them, was it through a join?
Yes there are filters, the table is filtered by warehouse type, Country code, and by metric date(I only want the most recent date)
I think what is happening is that QS is breaking the SUM apart for some reason. In the most recent snip it shows that ONT1 flags for a “yes” at 771k but they actually should be at 958k, So it is excluding 185k. Which when I move things around then it shows me it split up. Which is odd. Should I be using a SUM function?
Hi @Jesse1 ,
Checking in. Are you still facing the issue regarding the comparison in a calculated field ? . We’d still like to help. If we do not hear back in the next 7 days, we will archive the question.
Looking at the 1st screenshot, I am wondering if there is a filter applied to show only YES ? . In this scenario, if you have 2 rows ( 771k , 184k ) . The data is filtering out the 184K . Hence the display is showing the total sum as 771k . You can use sumOver with a PRE_FILTER so that the total value is not affected by applying a filter.
Kind regards,
Koushik
