Here’s a table I’m developing that summarizes a few tables into one large-scaled table. A few of the important things I need to add are to group by Client ID and Name, as seen in the first two columns in the image above. I’m also adding the last order date to see churn risk. Their annual GMV is also added.
The next things I need to add are: GMV (past quarter), GMV growth (past quarter), # of orders (past quarter), Order growth (past quarter) as columns. The GMV values I calculated make use of the LAC-A function to group the value of each order by order ID to ensure I don’t have duplicates in my analysis.
To calculate order growth in the past quarter, I’m using this formula: periodOverPeriodDifference(count(OrderID), deliveryDate, MONTH, 2)
Order growth %: periodOverPeriodPercentDifference(count(id), dailyMenuForDateEnd, MONTH, 2)
When I add these two fields to my table, the following issue occurs:
I’m really confused as to how to solve this issue.
The other issue is regarding calculating spend in the last 3 quarters + spend growth. When doing this, I used the LAC-A function, which doesn’t work with the periodOverPeriod function:
How do I solve this issue?
The overall goal is to create a single table with the following metrics stated above, please help as I’m relatively newer to quicksight
Additional info: Using this article which discusses about having multiple date ranges in the same table, I tried creating a calculated field which focuses on the previous quarter, as seen below:
Hello @rohit_SB, thank you for providing plenty of detail with the issues you are facing. I am going to start by specifically targeting the errors you are experiencing and we can go from there.
In regards to the first error Table calculation attribute reference(s) are missing in field wells, this means that fields you are using in the calculated field need to be used in the table so that the visual can reference those values. I’d start adding in the fields that you are using in the calculation 1 by 1 until it is able to load. Then you can make sure only the required fields have been added and then select the 3 dots on each new field that you do not want to display and click “Hide from visual” to avoid displaying anything unnecessary.
Now for The operation related to Level Aware Calculation (LAC) agg function is not supported, it seems like aggregations are being incorrectly nested into your periodOverPeriod calculation. How are you building the {GMV (USD) (LAC-A)} field and what are you trying to accomplish with this? We may need to find a different way to get that value so you can utilize the periodOverPeriod function.
As for the last error, Mismatched aggregation, I think the information for the {GMV (USD) (LAC-A)} field will give me some more information, but that basically means, if you are using an aggregated field, both fields in the comparison must be aggregated in the same way. I am getting the sense that your calculated field is a LAC-W field rather than a LAC-A field and that is why an error is occurring.
Here is some info on Level Aware Calculations in QuickSight. Once I get some more information, we should be able to figure this out. Thank you!
Hi Dylan! Thank you for the response! I’ll add on all the fields I’m referencing and update you based on that
The GMV LAC-A value is being calculated from the order IDs. The formula I’m using to calculate GMV is
min(GMV-USD, [orderIdD])
The reason why I initially calculated GMV this way is because I’m joining two tables called orders and order items, as I need certain fields on both. When joining, each order ID (a row), consists of several order items, which causes the GMV value to blow up (duplicate) due to the order items quantity. I have a previous thread asking this question as well, where David helped out greatly.
Similarly, when I’m trying to calculate average spending per pax based on clients, which can be calculated by taking the GMV (LAC-A) value I stated earlier and dividing it by the total number of items (which could be attributed to the pax based on an assumption we’re making).
Here’s the formula: GMV (USD) (LAC-A): min(GMV (USD), [{orderID}])) totalItems (LAC-A): min(totalItems, [orderID])
To get the average spending per pax, I simply do this: GMV (USD) (LAC-A) / totalItems (LAC-A)
Ideally, I’d expect smaller values. For example, row 1 for client 1, should be somewhere around 2.9USD/pax. How do I solve this issue? @David_Wong@DylanM Thank you!
Hello @rohit_SB, I think it is likely you will need to do some calculations with LAC-W to get the values you are looking for. I don’t think the periodOverPeriod functions are going to work because of the partitions you are requiring to get your values.
First, to get values related to last Quarter and last month you could create a field that converts your dates into Quarter and Month, then another calculated field for each to return only the period you are looking for: dateQuarter = truncDate('Q', {date})
Then check if it is last quarter: lastQuarter = ifelse(dateDiff({dateQuarter}, truncDate('Q', now()), 'Q') = 1, {dateQuarter}, NULL)
Now get the value for that date (function might differ based on your desired output): quarterGVM = sumOver(minOver({GVM}, [{orderID}], PRE_AGG), [{lastQuarter}], PRE_AGG)
Then if you want the difference between last quarter and this quarter, you would just check if that dateDiff function equals 0 rather than 1 and run another sumOver function on that calculated field. Then you should be able to subtract them.
Let me know if that gets you closer to the value you are expecting. The LAC-W functions work great for aggregating on certain partition fields. You just need to make sure that when you are using them, both aggregations that you are using are LAC-W or you will get errors.
If my answers lead you to your desired output, please mark one as the solution. Otherwise, let me know if you have any follow-up questions. Thank you!
Hi Dylan, I’ll try this out in a day or two and update you, will leave it as unsolved and notify you if there’s any issues I face in this process. Thank you!
Hi Dylan, I’ve tried some of the suggestions. Your recommendation on this formula worked well: minOver({GMV}, [{orderID}], PRE_AGG)/minOver({totalItems}, [{orderID}], PRE_AGG)
For the missing referencing in the table, here’s the issue that happens.
The missing field is just one, which is the date. My table shouldn’t consist of the date as I’m generally referring to the MoM growth. When I add the date field, it changes the table’s structure as the date also becomes a grouping factor, which basically takes away from the goal of creating this table. Is there another solution to getting the MoM growth without having to include the delivery date in the table?
Besides that, I did manage to understand how to calculate the quarterly GMV or # of orders placed in the last quarter using the following technique:
quaterlyGMV = ifelse(dateDiff(deliveryDate,now(),"Q") = 1, {GMV (USD)}, 0)
quarterlyGMV (LAC-A) [this helps with dedpulicating the values] = max({quarterlyGMV (USD)}, [id])
Hello @rohit_SB, since you posted a new question in the community on this issue, I will archive this topic and try to provide you the assistance you require on the new thread. Thank you!
