I have to convert a Tableau dashboard to Quick and I am facing the below issue with one of the calculation.
Tableau formula:
Numerator: SUM({INCLUDE [Sold To Account]:avg([last_12_vit_paks])})
Denominator: SUM({ FIXED [Install Status Sfdc], [Vam], [Product Index] : SUM({INCLUDE [Sold To Account]:avg([last_12_vit_paks])})})
Final Calculation: Numerator/Denominator
I want this in a KPI/Insight visual .
Here, the denominator value doesn’t change by parameter min and max values in Tableau. I used PRE_FILTER even though my calculation for denominator changes by parameter values.
Below is the formula for parameter.
withinweeksremaining=
if IF ISNULL([Latest Coverage Weeks Until Expiry])=TRUE THEN 0 ELSE [Latest Coverage Weeks Until Expiry] END
>= if [MinWeeksRemaining] <= 0 then -9999999 else [MinWeeksRemaining] END
and
IF ISNULL([Latest Coverage Weeks Until Expiry])=TRUE THEN 0 ELSE [Latest Coverage Weeks Until Expiry] END
<= [MaxWeeksRemaining]
then 1
else 0 end
Issues I face:
Denominator values also changes by parameter min and max because of that, my final calculation always becomes 100 %
I want to bring it in a insight visual and not in a Table.
I don’t want to do the calculation in custom sql/dataset level.
Hello @Kripa, I need a little more information about the expected output to assist you more here. I am not super familiar with the Tableau calculations, so I don’t totally understand what you are wanting to calculate.
Can you tell me, based on the values you created in the Arena dashboard, what you want the numerator and denominator to return separately? My initial thought is that you are trying to get the average value for sales, grouped by SA, then sum all of the averages to figure out the percent of the total averages, is that correct?
Also, you have the right idea in regards to utilizing the PRE_FILTER aggregation level. We should be able to utilize LAC-W functions to make sure the denominator value ignores the filters you have applied to the visual. If you want to include some, but not all, filters you can use this as well. Then, you can check for the filters you want to use in an ifelse statement.
I think we can make this work as expected, I just want to make sure I understand what you are looking to accomplish!
Yes you are right that I am finding out percent of total by taking average grouped by those dimensions. I used numerator and denominator separately just to debug and show you the calculations used in Tableau.
When I was trying replicate the same in QuickSight, my percent values always becomes 100% with whatever filters I apply.
So, I figured out the denominator value is causing the issue.
In denominator, I want to do an average of sales grouped by SA,PI,ISS,V and then take the total by PI,ISS,V. I am not able to bring this calculation and keep it constant. Can you help with this calculation? Otherwise if any other ways to calculate the percentage value. I even tried percentoftotal function and this too gives 100% for any applied filters.
Hey @Kripa, I adjusted the calculation for the Sum of Average calculation to also be PRE_AGG, that allows you to dynamically set the percent value depending on the SA value in the dataset:
Now, if you want this to ignore the filters in the dashboard, you should set both the numerator and denominator calculations to PRE_FILTER instead of PRE_AGG. Making both calculation levels match here is going to be the key to getting results that make sense.
Thank you. I tried PRE_FILTER and I agree the denominator values stay constant with the min and max filter.
Since we used PRE_FILTER, we are using min as aggregation. When I put it in a insight visual, it doesnt give me the total of denominator instead it just gives min value.
I want to use this calculation in a insight visual with some narratives.
When I try to sum this, the numbers are high due to duplicates.
How can I fix this?
For example, in this sample dataset, the denominator value should be 1060440 which should be constant with the min and max parameter filters. Now, when I filter it to 9 and 999, numerator will be 312,583 and denominator will be same 1060440. Only then I get correct percent values.
Right now, I get the constant denominator for each row by row in a pivot view. But, I need it in a Insight visual which is overall value and not row by row.
Let me know if you can help with this and any other details required from my end
Hello @Kripa, how do you want this to be managed when it is in a KPI? When using the LAC-W calculations, they need to be displayed as a min or max value, because it will repeat the same value across every row within the matching SA grouping since we use that in the brackets within the function.
I am not totally sure how you want this to work in the insight visual/KPI visual. Do you want the average of the averages across each SA? This would be why you were getting 100% previously, because it would sum all of the averages and divide it by the sum of the averages.
We need a way to group the denominator values by SA in the insight visual. You can add calculations like Top N or Bottom N to show 3 different denominator or percent values by SA. Something like that could work.
I just don’t totally understand how the min and max filters are supposed to impact the value.
Let’s not consider the min and max filter also for now.
Now, When we remove SA from table visual, the totals for Sum of Avg calculation (setting totals to sum instead of default) is 1060440. I want this in a insight visual.
Basically I need to see the totals of averages instead of seeing row by row.
Let me know if you need any other information from my end.
Even if I put top 1 filter, will I be able to see the totals of this calculation for each row in a insight visual.
Hey @Kripa, if you want the number to match the total you are seeing from the table, you need empty brackets in your sumOver function so it will total averages across all of the groupings. You can see the correct result here: Percent Calculation
I got to understand this now. The issue is that, in this sample data we just have only the fields involved in this table and it gives correct values for “sum of avg”. But in my dataset there are lot fields and I did try the same syntax. The “sum of avg” is not giving correct values and it gives higher values.
I think its because, the avg calculation itself has duplicates and we used minOver. Even though we use minOver it still has duplicates and that’s why we use min. So, above this when we use sumOver it gives higher numbers.
Hey @Kripa, you might be able to do a mix of calculations here:
Avg of Sales = avgOver({sales}, [{ISS}, {V}, {PI}, {SA}], PRE_AGG)
Sum of Average = sumOver(minOver({Avg of Sales}, [{ISS}, {V}, {PI}, {SA}], PRE_AGG), , PRE_AGG)
Potentially, a mix of the calculations could get us to remove duplicates.
Actually, another option would be to do a denseRank and filter to exclude all values that aren’t equal to 0.
Now, create a filter for Rank Averages so the value must equal 1 and exclude NULLs. That should remove duplicates from each group so you only count each value 1 time!
However, I wanted to exactly describe issue with mocked dataset with all the fields.
As I mentioned before, the calculation you shared is working only for the dataset which I shared earlier because it has only the fields to be included in the visual.
My actual dataset has lot of fields and I think due to that granularity it is causing mismatch.
Could you please help me with avg of sales and sum of avg in a KPI or insight.
Also, the sum of avg calculation repeats for STA for each PI,Status,V right..this should not change by min and max parameter and stay constant. Only then percent value will show else it becomes 100% for all filters.
Hey @Kripa, what is the field that is the most unique in this dataset? Is there a specific ID field that is unique on every single row or most rows in the dataset? In order to filter out duplicates I need to figure out what field I can use to do so.
Just checking in as it’s been some time since the last update on this thread. Were you able to review and implement the suggestions shared above? If so, did they help resolve the issue?
Feel free to reach out if you have any additional questions or need further clarification. If we don’t hear back within the next 3 business days, I’ll proceed with marking this thread as resolved.