Last month (when the data is uploaded on the 15th)

user07290
user07290 Member
in Archive

When I try to select show last month but because of the way my data is uploaded (e.g. January data is uploaded on 14th February, February data is uploade on 14th March etc) it doesnt load any data between the 1st and 14th of the month when you selct "last month".

Is there perhaps a beastmode I could use to achieve this instead?

Comments

  • Valiant
    Valiant Coach

    What's the actual date range you would like to display? 

    If you uploaded on Feb 14, would you want to see Jan 14-Feb 14 or Jan 1 - Feb 14? 

     

    You could change the timeframe to something like Last 30 days. 

     

    The Last Month option would look at it as Jan 1st to Jan 31st. 

     

    Once I know a bit more about what behavior you want it to have, I should be able to help you a bit more with the BeastMode or SQL transform that may be needed.

     

    Sincerely,

    ValiantSpur

     

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

  • user07290
    user07290 Member

    I would want to see 1st Jan - 31st Jan, which is fine after the 14th once the january data is uploaded but before then (February 1st-14th) I want to continue showing Decembers data (1st Dec - 31st Dec) because January's data isnt available yet (It's a pain I know)

  • Valiant
    Valiant Coach

    Ok, so to do that you would need to create a data transform.

     

    You could do something like this:

    Transform 1:

     

    SELECT *, MAX(`date`) as MaxDate
    FROM dataset

    Transform 2:

     

     

    SELECT * from transform 1 WHERE MONTH(`date`) = MONTH(`MaxDate`) AND YEAR(`date`) = YEAR(`MaxDate`)


    This would limit your data so you could display date range for 'All Time' and have it always be your most recent month of data. 

     

     

    If you wanted to not limit at the dataset level but use a BeastMode filter, you could do just Transform 1 and then in BeastMode do:

     

    CASE WHEN MONTH(`date`) = MONTH(`MaxDate`) AND YEAR(`date`) = YEAR(`MaxDate`) THEN 1 ELSE 0 END

    And just filter on 1

     

    The reason you can't just do in BeastMode is the MAX function is an aggregation function, which is invalid when using a BeastMode as a filter.

     

    Hope this helps. Let me know if you have any other questions.

     

    Sincerely,

    ValiantSpur

     

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

     

Categories