I have a dataset with week, customer_id, amount, itemname, and category fields.
I want to create a time-series chart for the week vs avg weekly amount spend in menuitem and category i.e
sum(amount)/ distinct customer_id group by itemname
sum(amount)/ distinct customer_id group by category
but getting LAC error.
i tried creating these calculated filed but not getting output as expected.
sum(AMOUNT,[{WEEK},${menu_item_name}])/distinct_count({registered_account_id},[{WEEK},${menu_item_name}])
Here menu_item_name, and category in the formula are parameters created and added as a control. below are the same data and corresponding expected output.
sample data and output:
The visual should be x-axis: week, y-axis: weekly average amount for menuitem and corresponding category they fall into.
let’s say the user selects coffee as an menuitem then the category would be HOT BEVERAGES. and the single visual should have bof this line chart.
Hello @dna, welcome to the QuickSight Community! I think I am understanding what you are trying to accomplish here. Have you tried using the avgOver or sumOver functions in QuickSight? They may be super helpful when trying to handle scenarios like this.
Also, is there a reason why you are using a parameter to sort by category? You could partition by the category field and then filter the visual using the parameter and you may see better results. My suggestion for the weekly amount that you could try would look something like this: sumOver({amount}, [{week}, {itemname}], PRE_AGG)
Then for the weekly average per guest, you could try this: avgOver({amount}, [{week}, {itemname}, {guest}], PRE_AGG)
Thank you for the reply. really appreciate it.
But i dont want weekly average per guest, What i want is the average weekly amount spent in itemname and average weekly amount spend in category which is: sum(amount) for menuitem in a week / distinct customer_ID for a menuitem in a week
sum(amount) for category in a week / distinct customer_ID for a category in a week
Finally, create the calculated fields for the weekly average amount spent per item and per category by dividing the total amount sum by the distinct customer count for each menu_item_name and category.
Make sure to replace {Total_Amount_Menu_Item}, {Distinct_Customer_Menu_Item}, {Total_Amount_Category}, and {Distinct_Customer_Category} with the actual names of the calculated fields you created in steps 1 and 2.
After you have created these calculated fields, you can use them in your time-series chart. Select ‘week’ for the x-axis and the calculated fields for the average weekly amount for the y-axis. You will also need to ensure that your parameters for menu_item_name and category are set up correctly and are being used to filter the visual appropriately.
Thanks I am getting an error while creating calculated field:
sumOver(sum({amount}), [{week}, {menu_item_name}], PRE_AGG)
For calculation levels PRE_FILTER and PRE_AGG, the operands can’t be aggregated
Hello @dna, one thing you can try is using LAC-A functions rather than LAC-W. Here is some documentation regarding the difference between the 2 types of functions.
You can try this:
For the sum the amount for each menu_item_name: sum({amount}, [{week}, {menu_item_name}])
Then for distinct customer IDs for each menu_item_name: distinct_count({customer_id}, [{week}, {menu_item_name}])
One more thing that you could try if you use the original LAC-W functions is to remove the sum function from within the sumOver field and it may eliminate the error.
@DylanM Thanks for the response. But It’s not getting me the expected output.
Basically I want to create a single visual time series for weekly average spend in menu_item and weekly average spend in category. there are one or more menu_item belongs to same category. For an example coffee and tea belongs to same category i.e ‘Hot Beverages’.
I am able to create 2 different visual one for menu_item and one for category using calculated field for each of these.
avg_menu_spend = sum({weekly_amount},[{week},{menu_item_name}])/distinct_count({registered_account_id},[{week},{menu_item_name}])
I created a visual with X-axis: week, Y-axis: avg_menu_spend and set a control parameter menu_item_name as a filter.
This is giving me weekly avg time series with user selected menu_item_name.
avg_category_spend = sum({weekly_amount},[{week},{category}])/distinct_count({registered_account_id},[{week},{category}])
I created a visual with X-axis: week, Y-axis: avg_category_spend and set a control parameter category as a filter.
This is giving me weekly avg time series with user selected category.
But I want to create only one single visual which will weekly avg menu_item and weekly avg_category.
And I can not set different filer for different filed in the SAME visual.
Pleas help me how to resolve this!
Thanks
Hello @dna, I think you would have the best results if you split them up into 2 visuals. If you wanted to keep them in a single visual, I think you would need to alter your dataset through SQL. It would require you to have a single column like menuType, to return either menuCategory or menuItem. When menuCategory is returned, your row values would contain values related to HotBeverages or ColdBeverages, and when menuItem is returned, it would displaye values like Coffee or Tea.
Each of these options would exist as options in a singular column and be joined in unions. This is a high level idea of what it would look like:
If you set your data up like this then x-axis is Date (WEEK), Value will be avgAmount, Color will be MenuItem. That will resolve the issue you are facing.
