Problem with a LAC calc

Question & Answer
, , , ,
1

I’m trying to get some LAC calculations to work but I’m hitting a wall. I have a viz that has a date-range filter and displays metrics of employees. One metric shows the % of Total Sales for each employee. I also have an Action Filter that allows users to filter on a specific employee to show the Sales details for that employee in another viz.

The denominator for the % Sales calc is TotNet :: sumover(Sum({Net Sales}),). But when the Action Filter is activated TotNet changes to the total for that employee. I’ve tried PRE_FILTER but no joy. What am I doing wrong?

2

Hi @gprice ,

Welcome to the QuickSight Community!

Can you try sumOver({Net Sales},,PRE_FILTER) to calculate the denom?

Thanks,
Prantika

3

As I mentioned in the OP I tried PRE_FILTER (and out of desperation PRE_AGG) but neither worked. But thanks for trying.

4

I have tried out the same solution in this sample dashboard. You can check this out.
32684 sample solution

Also, please share sample dashboard in Arena with more details to help you debug the issue.

5

Good morning (for me at least) Prantika. The analysis I’m working on is client data so I can’t upload it without first having to mock-up a suitable dataset. That said, I tried PRE_FILTER again this morning and it behaved completely differently than it did yesterday. Closer but still not correct. The analysis has 2 Filter Controls overall: Transaction Date Range and Store. Under those, there is the Action Filter for Employee. When I use the sumover(PRE_FILTER) it ignores all the filters and I get a total for all dates and stores. I just want the total for the specified date range and selected Store, of course. I’ve tried using a partitioned sumover: sumover(SUM(net_sales),[{employee}]) but that doesn’t work either. Any advice? I feel we’re close.

7

Yes, you are on the right path to partition by the fields.

Can you check out a similar solution I shared earlier? I recall, the use case was close to what you mention here.

Or if you can mock up some data to try out, I can give that a try. Based on what is your data granularity, the aggregations will also vary.

8

Hi @gprice,
It’s been a while since we last heard from you, are you still facing issues or were you able to find a work around for your case?

If we do not hear back within 3 business days, I’ll go ahead and close out.

Thank you!

9

Hi @gprice,
Since we have not heard back, I’ll go ahead and mark as solved. However, if you have any remaining questions, feel free to create a new topic in the community and link this discussion for relevant information.

Thank you!