2

Count of last three months regardless of year

I have two filters year and month for few years where I want to find the count of members for the last three months . For say if am selecting year 2023 in the filter and the month December in the filter it has to calculate the last three months count dynamically based on the selection  I.e. it has to calculate the October ,November and December count of 2022  . How to achieve this  ?

10 replies

null
    • Senior Director of Product
    • Ian_Macdonald
    • 4 mths ago
    • Reported - view

    Hi 

    Just to make sure the above is not a typo, you want the count of the three months counting backwards from the month selected of the year preceding that selected?

    Above you have said the selected month is December and Year is 2023 and you want count Oct - Dec 2022?

    So if you were to select July and 2022 you would want the count May - July for 2021?

    Ian

    • Immanuel_Gem
    • 4 mths ago
    • Reported - view

    Yea that's right , lets say if I am selecting the filter year (e.g. 2021)then another filter - month (January ) I want the member count aggregated for the previous three months i.e. ( 2020 dec ,2020 nov , 2020 oct) 

      • Senior Director of Product
      • Ian_Macdonald
      • 4 mths ago
      • Reported - view

       

      I'm still not entirely clear on your algorithm for selecting the months to count. Are you saying it is the three months prior to the year/month selected? Also, do you have a dimension of year-month, i.e. it has values like "2023 Jan", "2022 Dec"  etc., or are they separate dimensions of year and month, where month has the values "Jan" through "Dec"? What are you counting?

      Thanks,

      Ian

      • Immanuel_Gem
      • 4 mths ago
      • Reported - view

       yes three months prior to the Year/Month Selected …yes I do have separate dimensions of Year and month.

    • Immanuel_Gem
    • 4 mths ago
    • Reported - view

    I hope the query is clear now if not I can refine it further ?could you able to suggest some ideas for the same ..

    If the selection criteria is  year - 2024 and month - January:

    Result criteria: count of (January 2024, December 2023, November 2023)

    If the selection criteria is year - 2024 and month - February:

    Result criteria: count of( February 2024, January 2024, December 2023)

    If the selection criteria are year - 2024 and month - March:

    Result criteria: Count of( March 2024, February 2024, January 2024)

    This should be based on the dynamic selection.

    • Senior Director of Product
    • Ian_Macdonald
    • 4 mths ago
    • Reported - view

    OK, that's clear now. Do you have a dimension of year-month? And what are you counting?

      • Immanuel_Gem
      • 4 mths ago
      • Reported - view

       I have dimension of year and month separately...  I am counting the total members using the member ID(Row Count)

    • Senior Director of Product
    • Ian_Macdonald
    • 4 mths ago
    • Reported - view

    Hi 

    OK.

    First off, if this is a model you have built, then it would be much, much easier if you created a dimension of year-month or alternatively easier if you have a year --> month hierarchy. This is because then your months will automatically wrap around your years.

    Second, if this is not your model and it is based on a SQL DB, do you have a Date field in your tables? If so, then you can create a year-month dimension dynamically using custom columns and progress as above.

    Thirdly if the answer to all the above is no, then we have to determine what the combination of month and year for each of the prior three months should be from your selection. This is best done using parameters to select the month and year and using these values to define the combination of month and year to use in the calculation.

    First create parameters for Year and Month:

    We now need to think about the rules for defining the selection of Month and Year. We need the prior 3 months to the selected Month and Year. So if the User selects anything other than January, February or March, then all the months will be coming from the selected year and we don't have to worry about it. If the user selects January, then the prior 3 months are all in the previous year. If February, then November and December, if March then just December. So we need to test for the month selected and assign the year accordingly to what is selected. 

    A we have 4 options to choose from, I'm going to use the CASE statement rather than nesting loads of IFs. Pyramid constructs its CASE statement by stating the Criteria in  a list, then the matching results in  a second list. We can test which month was selected in the parameter by testing its caption. The first criteria tests that the selected month is not in Jan, Feb or Mar, i.e. it is one of the months where we don't have to think about the year.

    We then test to see which month from Jan, Feb or Mar was selected:

    We now list the results we want to match the Criteria. If it is not Jan, Feb or Mar, then simply add the previous 3 months using the selected year together and using the Lag function on the month parameter. Note the little "fx" sign on the row when the parameter is selected. This allows you to specify the month parameter with a lag, lead, previous, next function. So the three months are the selected year parameter for all, with the month parameter with lag 1, 2 and 3 assigned respectively.

     

    We now need to deal with Jan, Feb and Mar.

    For January, we need to select Oct, Nov and Dec from the year previous to the one selected. We can specify each of the months explicitly, the year being defined as a lag(1) function on the Year parameter.

    For February, we need Nov and Dec from past year and January from this year. Again use the lag function on the data points for November and December, but not for January.

    I've also used the dynamic caption here to make it easier to see what has been selected by using the parameter captions in the caption of the calculation.

    Here we can see the results. Note that the calculation is saved into the Month dimension, so it can apply to any measure.

    Hope that helps!

    Ian

      • Senior Director of Product
      • Ian_Macdonald
      • 4 mths ago
      • Reported - view

      Oops! I just noticed from your last post i still got the algorithm wrong! Same approach, but different lag options!

      Ian

    • Immanuel_Gem
    • 4 mths ago
    • Reported - view

    Thanks Ian ,It worked .

Content aside

  • Status Answered
  • 2 Likes
  • 4 mths agoLast active
  • 10Replies
  • 67Views
  • 3 Following