In this image Prior month and Prior Quarter is giving me fixed values , not changing according to the date selection.
Below are some calculations used :-
Total QM Evaluations PM = sumIf({QM Count}, DATE>={first_day_last_month} AND DATE<={same_day_last_month})
Total QM Evaluations PQ = sumIf({QM Count}, QUARTER={prior_quarter} AND YEAR={latest_year})
Hi
Follow the following steps to achieve your results. Create the month_key field: truncDate(“MM”, {Date}) Create the quarter_key field: truncDate(“Q”, {Date}) Create the latest_date_in_month field:maxOver({Date}, [{month_key}], PRE_AGG) Create the latest_date_in_quarter field: maxOver({Date}, [{quarter_key}], PRE_AGG) Create the is_latest_date_in_month field: ifelse({Date} = {latest_date_in_month}, 1, 0) Create the is_latest_date_in_quarter field: ifelse({Date} = {latest_date_in_quarter}, 1, 0) Apply the filters:
Go to the dataset and apply the filter is_latest_date_in_month = 1 to focus on the latest dates for each month.
Similarly, apply the filter is_latest_date_in_quarter = 1 for the latest dates for each quarter. Create the prev_month_metric field: lag({metric}, [{month_key}], 1) Create the mom_change field: {metric} - {prev_month_metric} Create the prev_quarter_metric field: lag({metric}, [{quarter_key}], 1) Create the qoq_change field: {metric} - {prev_quarter_metric}
By following these steps, you should be able to calculate the Month-over-Month and Quarter-over-Quarter changes for your metric based on the latest dates in each period
But I do have a condition prior Month should give values like if we select today 's date i.e. 9 Aug then in the prior month I should get 1july to 9 july data not the entire month .
Hello @Samiksha, I know our data isn’t exactly the same, but I put together some calculated fields in a table to show how I would manage the values for this month and last month. You can see the calculated fields I wrote in this demo analysis: Not able to resolve this problem with prior month and prior quarter
I will mark this response as the solution, but please let me know if you have any questions! I went with a sumOver aggregation rather than utilizing the sumIf function. I think that might work better for this situation.