Hi everyone, I’m trying to calculate the growth of GMV in the last quarter. To do this, I want to be able to calculate the total GMV for that specific month.
Based on the image above, the pivot table’s rows consist of client names and the columns consist of the delivery month with GMV for that corporate in that month. I need to calculate the growth based on the total GMV per client for that delivery month. Then, I need to calculate the GMV for a specific month. How do I calculate the GMV for a specific month? This was my initial formula: ifelse(extract("MM", {dailyMenuForDateEnd}) = '10', {GMV (USD) (LAC-A)}), but I’m assuming that my syntax is wrong.
Additionally, I also need to show an additional column that shows the total of GMV for the last 3 months.
Edit: periodOverPeriodPercentDifference(sum({GMV (USD) (LAC-A)}), dailyMenuForDateEnd, MONTH, 1)was the formula I decided to use, however this does not support LAC-A functions. I considered applying level aware aggregation after creating the period over period difference, but it does not work either.
Hi Naveen, I tried doing this, but the GMV values I have are calculated based on the LAC-A function as @David_Wong suggested earlier. The issue is that I can’t combine LAC with periodOverPeriod or periodToDate functions. Do you have any solutions?
Here’s something I did after looking at this thread
I created a new calculated field with the following formula: periodOverPeriodDifference(maxOver(max({GMV (USD)}),[orderID]),deliveryDate,MONTH,1)
Hello @rohit_SB, my apologies for the delayed response. I know I have worked with you on a few of your calculations more recently, is this something you are still seeking a solution for? With all of your calculations issues, I am starting to think that you may want to try and build your dataset using custom SQL to try running a few of these aggregations before manipulating them in the analysis. Are you able to alter the query with SQL or are you using a CSV for your datasource?
Hi @DylanM, This ties in with the previous MoM calculations I wanted to do. This is still something I need a solution for. However, we’re dumping all our data on Amazon S3 through ElasticSearch (if I’m not mistaken) and there’s no way for me to change any data (such as making aggregations) pre-Quicksight. It seems to be common practice to use custom SQL to make queries, but I’m not sure how I could do that here since the dataset is already available and has been transformed into a SPICE dataset.
Hello @rohit_SB, depending on your company, it may be worth considering utilizing Athena to run queries on your data in S3. That would allow you to build out some tables and run aggregations in SQL then bringing those tables into QuickSights as datasets. I know that is a few steps to add to the process, but with the functions you are currently trying to run, I think it would make building your dashboards significantly easier. QuickSight is a powerful application, but it often requires a little more formatting on the data side to take full advantage of the functionality.
Would it be possible for you to connect your S3 bucket to Athena and run queries that way?
I can bring it up with my supervisors and discuss this option with them. To me, it’s fine as long as it fits the company’s goals and data requirements. Thank you, i’ll update this thread soon!
Hello @rohit_SB, I appreciate the update! Let me know if you have any further questions, but I think this will give you a lot more flexibility and make it a lot easier to build out the metrics you require.
