I am looking at the columns “#DSP” and “dsp_count”. dsp_count is a dataset column.
My goal is to create a calculation which gives me the max value in the subtotal on DS level, per week, but the subtotal on OTR Mgr should give me the sum of each DS subtotal.
the column “#DSP” is an attempt to do that. until now I was able to get the max subtotal within a particular DS (in the first subtotal Dx1 = 13). but in the subtotal on OTR Mgr level, it should be the sum of those max, i.e., 13 + 11 + 24 (from Dx1, Dx2 and Dx3 respectively)
the current formula of #DSP is:
maxOver(sum({dsp_count}),[DS, {OTR Mgr.}])
I tried in several ways to implement a sum here, but was not successful so far.
Hello @Alexandre_Kniepert, so at the moment, what is happening is that the maxOver function is always going to return the max value rather than a sum of max values on roll-up. I am not certain if there is a work-around, but something that might work on the DS category level and the OTR subtotal level would be to nest the maxOver inside of a sumOver. There is a chance this will mess up the DS level, but let me know.
You might need a partition level for OTR Mgr in the sumOver as well and you can also try using without PRE_AGG on the aggregations if that doesn’t work. If this leads to the desired output, please mark my response as a solution. Otherwise, please let me know what issues occur and we can try to sort it out.
Hi @Alexandre_Kniepert
Just checking in on this topic, are you able to supply more details to answer @DylanM’s questions?
If we don’t hear back in 7 days your question will be archived.
Many Thanks,
Andrew
it actually did not work as well. I had tried several combinations, also with pre_agg and partitions, but could not reach the result I expected.
in the end, I decided to take another path: in the dataset I divided the number of dsp by number of days within a week and then I just added the new column as sum. Because I am not showing the table at day level, only week onwards, it is fine to average the number of DSPs within a week.
Otherwise, I still struggle to understand the application of pre aggregations in the measures, in general. I have some measures I (successfully) created using it, but it was pure try and error.
it can get especially complicated when we nest xOver formulas.
by the way, is it possible, in general, to add xOver measures in graphics such as clustered bar? It does not work well from what I see, especially if you have partitions like week, year which you do not want to necessarily show in the graphic and/or they are in drill-down.
Hello @Alexandre_Kniepert, I would say trying to figure out the best way and correct time to utilize LAC-A vs LAC-W functions can be complicated. For me it was quite a bit of trial and error, while also reading through documentation. What you ended up doing to resolve this issue is a route I will often take. Taking time to understand how to best create your datasets so that QuickSight is able to interpret your data more easily is super important. Don’t be afraid to build out more specific datasets to handle certain visualizations you want to utilize.
I do know using LAC-W functions can be helpful in clustered bar chart visualizations, but again it all depends on how your dataset is built out. I would highly recommend reading this blog post to understand more about the use cases for LAC-A vs LAC-W functions.
As always, whenever you face a problem with a specific visualization, please share your expected output, any error messages, and aggregation issues in the community, and we will help guide you towards a solution. I’ll mark your previous response as the solution for this specific topic, good luck on your continued learning of QuickSight!
