Hello,
I have daily transaction level detail data that I would like to use to create a moving average to compare incoming daily data to.
The previous day totals by Financial Class and WeekDay should be compared to the 13 week average of the same previous 13 matching week days. I have created a daily cash total average field using the avgover function but that is unable to be viewed by different financial classes in pivot tables and other visualizations. I believe I need to add a partition for Financial Class, any help would be appreciated!
Below is sample data where Week Day is a calculated field that I created and the rest are raw data. The totals for the Red financial class are listed in the table below. The formula I am looking to create should show that the Friday average for Red is 1,112.5 and Thursday is 25,390.15.
Hello @mkelley, welcome to the QuickSight Community!
I want to confirm that I am on the right track with this calculation. You want to be able to show the average amount for the last 13 weeks for Financial Class and by Weekday, am I correct? If so, you have a couple of options. First, you can either filter your table directly to only show the Transaction date for the past 13 weeks or you can use an ifelse statement to check if the transaction date is less than or equal to 13 weeks old, if yes, return the amount.
ManualDateCheck = ifelse(dateDiff({Transaction Date}, now(), 'WK') <= 13, {Amount}, NULL)
Now to get your average value, you are going to want to make sure we are partitioning by the financial class and week day fields in an avgOver function.
avgAmountByPartitions = avgOver({ManualDateCheck}, [{Financial Class}, {Week Day}], PRE_AGG)
That last calculated field should build out the column you are looking for. If this helped you solve the problem you were facing, please mark my response as the solution. Otherwise, let me know what errors you receive and we can sort those out. Thank you!
Thanks Dylan!
That formula is appearing to give me the average of each individual transaction line, but ideally I would be seeing what the average total for that specific Week Day and Financial Class combination is. Basically, what is a typical Wednesday total transactions divided between the financial classes. I am less concerned with the average of individual transaction lines, just the average of each financial classes total Week day.
Hello @mkelley, I was anticipating it to give you the value that you are expecting. Is your Week Day field a string value or a date field? If it isn’t a string, I would recommend converting it to one for this, and possibly try switching the order in the partition field from [{Financial Class}, {Week Day}] to [{Week Day}, {Financial Class}]. My assumption is that the previous function did not work because it isn’t grouping all Fridays but rather the Friday connected with a specific date. I think creating a calculated field that returns this as a string instead would help to avoid that.
Something you can do as well to test if the function is linking to the correct number of Transactions would be to run a countOver function instead of a avgOver function with the same partition fields, and check if it is referencing the correct number of transactions for each financial class in a particular week day.
Hi Dylan, my Week Day field is currently a calculated field that returns as text string. When I switch the partitions I am getting values to return but they are just the total sum of all transactions for those two columns of Financial Class and Week Day.
Should I be viewing the calculated avg over field as an average in my tables/pivot tables? or just a sum? Currently neither are validating out to my check calculation.
Hello @mkelley, there are a few things that can help with this to try and get the result you are expecting. If you are applying this calculated field to a visual that would take multiple rows with the same partition (where the returned value would appear more than once), you would want to aggregate the field well to be min, max, or average. Each row that would match the partitions you included will return the same value, so if you use sum it will add the desired value multiple times.
Another helpful hint for making this work would be to filter the visual you are implementing this calculated field in for the ManualDateCheck field. To make sure I am only aggregating the data I want, I would add a filter for that field, set it to a custom filter, select does not include and enter an impossible value, then select Exclude Nulls for the final dropdown. This ensures only the Weeks you want included will be utilized in the aggregation. Let me know if that resolves the issue you are facing!
Hello @mkelley, since we have not heard back from you, I will go ahead and mark my previous response as the solution. If you are still having trouble implementing this or have any follow-up questions regarding my suggestions, please let me know. Thank you!