I need to compute the P50, P80 distribution of # of transaction count per consumer over a rolling period of last 3 months, and trend over months.
My raw table contains records with following fields
transaction_month
consumer_id
transaction_id
I created a calculated field called transaction count_per_consumer:
windowSum(distinct_count({transaction_id}), [{transaction_month} DESC], 0, 2, [{consumer_id}] ).
And i want to create another calculated field over it called P50_transaction_count_per_consumer:
percentileOver({transaction count_per_consumer},50,[{transaction_month}]).
However, when i tried to visualise the P50_transaction_count_per_consumer by transaction_month on a chart, i get the following error:
|sourceErrorCode:|VISUAL_CALC_REFERENCE_MISSING|
|sourceType:|SPICE|
@Shiyan - Thank you for posting your query. It seems that the fields that you are using to calculate are not part of the visual; hence it is giving that MISSING_REFERENCE error. This is my initial hunch based on the details that you provided. Can you please see if this problems gets resolved when you add consumer_id in the visual. Let me know if that helps!
Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!
I am able to display the chart now. However, my intention is not to compute nor show P50 for every single individual. It is meant to show P50 across the consumers.
Hello @Shiyan - In that case, can you please share a sample dataset and the visual details where you are expecting to see the value. If possible please share the expected outcome as well so that I can try to replicate exactly what you are trying to do. Thanks!
Sorry @Shiyan - I could not access the Sample Data that you shared as it is giving access denied error. A Sample snapshot with 8 - 10 rows would also work in case that makes sense.
When you say just show consumer here that you mean you want only to show it across the whole field as a total of sorts.
To achieve this I believe you would need to either remove the customer_id partition from your first calculated field or remove the customer_id field from your field wells for the visual.
For now I am going to mark my comment above as the solution, but if you still need help with this problem please feel free to repost in the community. You can link to this topic.
