Hi there,
I would like to find a way to find the orders from a year ago based on a custom period please see the screen shot below of how my data is structured. I would like to find the orders from -13 period ID ago. I would then like to graph the results comparing the periods from the current year to the same period in the previous year
Hello @JSharman, do you have a date field in your dataset, or is it just the custom string field that you created for period? If you have another date field that is accessible within your data, you could utilize that to bring in a field from the same date last year with the lag function. That would be the best way to manage this and would utilize built in functionality.
Otherwise, you may be able to do something using LAC-W functions. What I would say is create a calculated field that, depending on how your data is set up, would either run a distinct count of orders per period, or a sum of orders per period. It would look something like this:
sumOver({Total Orders}, [{Period}], PRE_AGG)
Then you could try plotting that field on a graph and use the Period Group as your group by field and filter based on that as well. That would then likely give you the result for both periods within the period group. Let me know if that helps!
Hi Dylan,
Thanks for your response, when I create that calculated field and plot it using the Period group as the group this is what it returns. I don’t have another date field in my data set as the Period field slightly changes each year.
I already have a calculate field in my dataset called Transactions vs Previous year that calculates the percentage difference from 13 periods prior. Is there a way I can modify this to get the raw number rather than percentage.
percentDifference(
sum(totalOrders), // Directly use the aggregated measure
[{Period ID} ASC], // Ensure that Period ID is sorted in ascending order
-13 // Lookup index of -13 to look at the previous year
// No partition field unless needed
)
Hello @JSharman, did you try to utilize the lag function to grab the value from 13 period IDs previous? That would allow you to aggregate the difference between that value and the current period value since they would exist on the same row.
It would also be helpful if you could put a demo version of this analysis in Arena so I could test some solutions locally. If the lag function doesn’t work, I think that would be the best course of action. Thank you!
Thanks @DylanM
Yes using the lag function seems to work to get the total orders from the same period last year, however when I try to graph the fields total orders and total orders last year I get this error below. Is this because there are gaps in my dataset when it looks back to older periods?
region:eu-west-2
timestamp:1714749065756
requestId:e834bd00-c4f6-471c-80b3-6ff553d820f9
sourceErrorCode:VISUAL_CALC_REFERENCE_MISSING
sourceType:SPICE
Hello @JSharman, this is linked to one of these possible issues. Either, the lag function isn’t going to work because it isn’t receiving an actual datetime datatype field, or you are missing a required field that is being utilized in the calculated field within the visual itself.
@DylanM You’re correct the lag function isn’t receiving a date time field this was my Calculated Field where I used period id which is just a number
lag
(
sum(totalOrders),
[{Period ID} ASC],
13
)
Hello @JSharman, I am honestly not sure if there is going to be a working solution for this due to the lack of a date value in your dataset. My first recommendation would be to manage this issue in custom SQL and add a column in each row that will bring in the value for last year if the Period Group matches. Then you would be able to aggregate the values easily.
The only other thing I can think of is to try and bring the value on the same row using LAC-W aggregations. I am not certain this will work, but this is my best idea for a work-around on this issue:
Period ID Last Year = ifelse({Period ID} > 13, {Period ID} - 13, NULL)
Period ID Last Year Value = sumOver({Total Orders}, [{Period ID Last Year}], PRE_AGG)
If my above suggestion throws an error or doesn’t provide the correct value, custom SQL or updating the view/table in your database are going to be the best ways to manage this. I’ll mark my response as the solution, but please let me know if you have any remaining questions on the topic.