Not able to resolve this problem with prior month and prior quarter

Question & Answer
, , ,
1

image
image1056×263 21.5 KB

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})

latest_month = extract(“MM”, {latest_Date})

latest_Date = maxOver({DATE}, , PRE_AGG)

first_day_last_month = truncDate(“MM”, addDateTime(-1, “MM”, {latest_Date}))

same_day_last_month = addDateTime(-1, “MM”, {latest_Date})

QM Count field has only values 0 and 1 .

single select date should be applied to these filters or not or it should be at the sheet level not on the visual level ??

Actually these values are not coming up properly if we change date these same for every date we select.

Someone please help me struggling from 2 days with this and people are banging my head continously.

2

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

1 Like
3

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 .

2 Likes
4

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.

1 Like