Need help to exclude the dimension that is used in the filter while calculating the derived column

Question & Answer
, , ,
1

Hi All, I am new to Quicksight, I need to create an calculated field ‘Cummulative Return%’
Nothing but (running_return_counts) / total_shipment_count .

I am deriving running_return_counts partitioned by Supplier and Manufacture months
So I was using SumOver([return_counts)],[{Supplier},{mfg_month}] PRE_AGG)

Also I have a dimension in the filter called days_TT , I dont want this filter to affect my calculation.. Even using PRE_AGG , it is been included in the calculation

Need help to Exclude this filter dimension while calculating the cummulative return

Thanks!

1 Like
2

Hello @RajiniN, the PRE_AGG calculation level doesn’t ignore filters, it runs the aggregation after filters are applied to the visual. If you wanted filters to be excluded from the aggregation, then you want to set PRE_FILTER as your calculation level. Does that resolve the issue you are facing?

3

Hi @DylanM , I tried with PRE_FILTER option, it didnt resolve the issue.
Thank you!

4

Hello @RajiniN, I appreciate the follow-up, but please provide some more information so I can suggest an alternative option. With some added context on the result, what is wrong with the returned values, and any error messages, I can assist you further.

Something that would be helpful is to know if there are other filters included on the visual that you want the calculation to account for.

6

Thanks @DylanM!

I am trying to create Cumulative Return%.

I created a cumulative_return = sum({return_count} , [{day_until_failed} ASC } , [{Supplier},{Month}] )

Cumulative_return% = cumulative_return/ sum( total_shippment)

Then I need to display in the visual only for day_until_fail = 10 ( Basically I need to apply a filter ) which should not affect our cumulative_Return% calculation

So I tried using SumOver and Pre_agg and SumOver and PRE_FILTER , No use still the filter is impacting the calculation.

That is the issue

Thanks!

7

Hello @RajiniN

Were you able to find a solution to this or are you still working on it?

My bet is that you need to add PRE_FILTER rather than PRE_AGG to one of the calculations you made using the LAC functions. Can you share those calcs?

8

Hello @RajiniN, I see you keep mentioning cumulative return, are you using any runningSum calculations as well? That type of aggregation doesn’t contain a calculation level option to set PRE_AGG or PRE_FILTER, so it is possible that could also be causing an issue with ignoring the filters.

1 Like
9

Hello @RajiniN

Were you able to find a solution or do you still need help? It’s been a few days sine we have heard from you.

If we do not hear from you in the next three business days this topic will be archived.