Limited date options

user02467
user02467
edited March 13 in Datasets

1. Year to Date and Current year seem to yield same results.  Is this a bug?

2. Common period measure of MAT (moving annual total) and MQT (moving quarterly total) seems to be missing.

3. Can system recognise what is the latest completed month, instead of admin manually adjusting period when new monthly data arrives?

Best Answer

  • ST_-Superman-_
    ST_-Superman-_ Coach
    Answer ✓

    1. Year to Date and Current Year will be the same results if your data set does not have any future dates in it.  If you have data points from July and August, etc. and you select "Current Year" they will show up in the results.  If you select "Year to Date" they will not be in the results.

     

    2. Moving Annual Total - We usually use the "Last 365 Days" option to get a "rolling 12 month" view of sales, etc.

     

    3. I set up a beast mode to filter out the current months data.  Something like this:

    CASE WHEN YEAR(`Invoice_Date`)=YEAR(CURDATE())

    AND MONTH(`Invoice_Date`)=MONTH(CURDATE()) 

    THEN 'true' else 'false'

    END

     

    You can then put this field in your "filters" section and select to only look at the "false" results.  This will then automatically include June dates once we get to July 1st.


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman

Answers

  • Valiant
    Valiant Coach

    Year to Date and Current Year should return the same as they're the same time period.

     

    Moving Annual and Moving Quarterly can be done by choosing Last 12 Months (or Last 4 Quarters or Last 1 Year or Last 365 Days), same for moving quarterly. There are many ways to return the same time period.

     

    If you want the last completed month, just use the Previous Month as your default time period.

     

    image.png

     

    Hope that helps,

    ValiantSpur

     

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.

  • ST_-Superman-_
    ST_-Superman-_ Coach
    Answer ✓

    1. Year to Date and Current Year will be the same results if your data set does not have any future dates in it.  If you have data points from July and August, etc. and you select "Current Year" they will show up in the results.  If you select "Year to Date" they will not be in the results.

     

    2. Moving Annual Total - We usually use the "Last 365 Days" option to get a "rolling 12 month" view of sales, etc.

     

    3. I set up a beast mode to filter out the current months data.  Something like this:

    CASE WHEN YEAR(`Invoice_Date`)=YEAR(CURDATE())

    AND MONTH(`Invoice_Date`)=MONTH(CURDATE()) 

    THEN 'true' else 'false'

    END

     

    You can then put this field in your "filters" section and select to only look at the "false" results.  This will then automatically include June dates once we get to July 1st.


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • user02467
    user02467

    Thank you for your excellent solution.  Regarding beast filtering out current month, how would you modify the code if I also wanted to filter out previous month?  Thanks again.  Andrew

Categories