Divide values from other rows

Hi All,

I have an issue here is some example data

Example

I need the expected column which is taking value from Column2 A row…
because I will need to divide the Column 2 and Expected column, with A row as the basis value.
This column expected is derived from a parameter as true or false, so if user select parameter A, then the expected column will become 12, if user select parameter B then the expected column will become 7.
In tableau we can use EXCLUDE syntax. but I can’t found a way to do it in quicksight.
Please help

Hi @consultantcon -
You can use ifelse logic. Documentation and examples can be found here. If I understand your question correctly, the expression would be something like below. You can next ifelse statements for more options of evaluation.

ifelse(${parameter_value}=‘A’,12,7)

1 Like

Hi Kelly,

Thanks for your reply, but your calculation will become like this
image

But I want it all rows become 12 not 7
I already tried
ifelse (column1 = $(parameter_value), column2, 0) but it’s not working

@consultantcon -
You want all rows to become 12 based upon what condition? I’m not clear on how you know you want the value of Expected to be 12.

1 Like

Hi @Kellie_Burton ,

yes the expected is based on parameter.
as mentioned in the post, I will re-type it

  1. if user select parameter to A value, then the expected column all values will become 12
  2. if user select parameter to B value, then the expected column all values will become 7
  3. if user select parameter to C value, then the expected column all values will become 5

All this value is needed to divide the column2 and expected column

Thanks

Hello @consultantcon - Thank you for posting your query. If I understand your problem statement correctly, then this could be a potential solution. This can be solved by creating a calculated field. Please review the below snapshot and let me know if this works. I have used my own dataset but the scenario is quite similar. Hope this helps!

Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!

1 Like

@sagmukhe Thanks!! this is the one that I was looking for!

@sagmukhe
Actually I got an error because the value is actually another LAC calculated fields
so it won’t work if the value is another LAC?
my LAC was
avg(value,[ID])
somehow it give me this error
“mismatched aggregation. Custom aggregations can’t contain both aggregate “‘NESTED_AVG’” and non-aggregated fields “NESTED_AVG (“Value”, “ID”)”, in any combinations”

Thanks for your response. Can you please share a sample dataset with the actual base columns and calculated column derivations? It will be easy to replicate at my end. Thank you!!

1 Like

Hi @sagmukhe thanks for the reply below is the sample data
image

and I have a calculation called “Avg Value”

avg(value,[ID])

and then the calculation I need based on parameter that I’ve done based on your solution

sumOver(ifelse(${paramActivity} = Activity, {avg value},0), , PRE_AGG)

and then this error message pop up
“mismatched aggregation. Custom aggregations can’t contain both aggregate “‘NESTED_AVG’” and non-aggregated fields “NESTED_AVG (“Value”, “ID”)”, in any combinations”

Thanks!

Thanks @consultantcon for the details. Now I see what problem you are facing. However, I was able to circumvent the problem with a new calculation logic based on the sample dataset that you provided.

  1. Change the “Avg Value” calculation to something like :

avgOver({ Value}, [{ID}], PRE_FILTER)

  1. Change the Expected Value Calculation to something like :

maxOver(ifelse({ID} = “A”, {Avg Value}, 0), , PRE_AGG)

Please see the snapshot of my implementation. Here “Avg Value” field holds your original calculation, “New Avg Value” field holds the modified calculation that I used to determine the avg and “Expected Value” holds the modified calculation that we should use to get the desired outcome.

Obviously you can implement the parameters like how I depicted in my older post. Here I have tested by hardcoding the input to “A”, which can be replaced by the parameters. Hope this helps!

1 Like

Hi @sagmukhe thanks for the reply,
my concern with avgOver is I need to pull the ID into the table to make the value work right?
Because the requirement for the chart is only to have activity and the expected value inside (line chart)
if I’m using avgOver I need to pull ID into the chart also, please correct me if I’m wrong…
And also the behaviour kind of different.
I put the result below because avgOver will average both A and C because it’s same activity.
image

while if I’m using avg, for A and C I will sum and I only average for ID A…

Hi, @Kellie_Burton @sagmukhe

would appreciate any suggestions!

Hello @consultantcon - The understanding gap that I had is that you are trying to leverage it in a line chart. Anyway, since you mentioned that you are looking to build a Line Chart with the 2 attributes - activity and expected value, does that mean that you are looking for a straight line parallel to X axis as the expected value is supposed to remain constant as per your ID parameter selection. Is that the right understanding?

1 Like

Hi @sagmukhe thank you so much for the reply.
let me detailed about the line chart, it will be
X axis - Year
Y axis - percentage of activity (number of activity / expected value based on parameter)
Legend/Color - activity
this would be the line chart.

Hi @sagmukhe
would appreciate if there’s any update

Hi @consultantcon ,

The following calcs will let you plot the percentage on line chart without including all involved fields in the chart.
Avg Value : avg(Value,[ID])
(Above calc is used just for comparison with Avg LAC-W calc - To show they match)
Avg LAC-W : avgOver(Value,[ID],PRE_FILTER)
Min Expected Value : minOver(ifelse(ID = ${pSelectedID}, {Avg LAC-W}, NULL),,PRE_AGG)
Percentage : sum(Value)/min({Min Expected Value})

You can see that the line chart is plotting just the activity and percentage.
You mentioned that you wanted to plot the year as well in the line chart. However, year wasn’t in your sample data and hence providing this view.
Just add year to the Avg LAC-W calc alongside ID if you desire to avg at year and ID level.

You will need to tweak little bit more to get the final state that you desire. (Say the percentage calc )
I believe the above gets you real close and hence marking this as solution to this post.
Please let us know if you have further questions.

1 Like

Hi Arun,

Thanks for the reply, but seems there’s wrong understanding.
For you the using ID parameter, but actually it should be activity…
If you remove the ID from the table it will shows different number between Avg Value and Avg LAC-W because it is different behaviour between avgOver and avg.

Hi @consultantcon,

If you remove ID from the table, the displayed values will of course change. What I was pointing out is that I didn’t have to use ID in the line chart.

Let’s take a step back.
The sample data you provided is
image

Please provide the value you expect to see for the intermediate calculation (the one you called expected value) when user select A, B and C for the above data.
Also, provide the percent of activity you expect to see against each activity for each of the above selection.

Once you provide this, I can take another look at how we can get the expected results.

Regards,
Arun

1 Like

Please refer to this post that I replied to sagmunkhe, I am using avg because I want the result as avg not avgOver.
Thanks