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.
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!
@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!!
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 @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.
Change the “Avg Value” calculation to something like :
avgOver({ Value}, [{ID}], PRE_FILTER)
Change the Expected Value Calculation to something like :
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!
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.
while if I’m using avg, for A and C I will sum and I only average for ID A…
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?
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.
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.
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.
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
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.
