Remove lookback period from the data and see the values.

Prathmesh24_Diacto · 2024-04-25T19:05:21+00:00

There was an error rendering this rich post.

I have a dataset which holds transaction data. I have dates, i have derived a week column from date which has values like “2024/17”, etc. and I usually filter data based on weeks. My use case is when I select a week using 2 variables- one will pick year and other will pick week number, I want to subtract lets say past 4 weeks (lookback period) of data and see rest of the previous data in my card.

For example: If I pick 2024/17 in the variable control, it should subtract past 4 weeks which is 2024/13 and show the rest of the data from 2024/13 and back.(it can go back to any data). Similarly, if I pick 2023/16, It should subtract 4 weeks and show rest of the previous data.

However, I am not able to think of how I can execute this in a beastmode with variables.

The reason for doing this is I know that if a product is sold 1 week ago it still has 3 weeks to be returned. So the return rate for that product will be very less now. But if I go back in time already i.e. 4 weeks, I can now see the return rate for the product whose return window is already completed.

Any help would be appreciated. Thanks a lot! Please let me know in case of question.

Find more posts tagged with

Accepted answers

david_cunningham

@Prathmesh24_Diacto - I just completed this write up for a similar question. I think you'll find it useful. I've adjusted it to fit your use-case.

This answer also expands on the requested functionality, by adding in a toggle that will allow users to flip between limiting the data based on a selected date, and showing all data. As well as allows users to specify the number of days to calculate back from the selected date.

We can use variables to allow users to select a date that will then limit the data shown. In this case, we are being asked to set it up so that the previous 28 days of data is excluded based on the user selected date.

Step 1-1: Create a variable (SELECTED_DATE) that will be used as a control to let users select a date.

Step 1-2: Create another variable (USE_SELECTED_OR_ALL) that will be used as a control to let users toggle between selecting a date, and showing all data. Set up 2 options (Selected Date & All Dates) for this variable.

Step 2: Use the SELECTED_DATE and USE_SELECTED_OR_ALL variables in a case statement. This case statement will be used as a filter in Analyzer. I called this Beast Mode (RELEVANT_DATA_FILTER)

CASE  
  WHEN USE_SELECTED_OR_ALL = 'Selected Date'  
  THEN    
    CASE WHEN date < = DATE_SUB(SELECTED_DATE,interval 28 day) 
    THEN 'Include' 
    ELSE 'Exclude' 
  END  
  ELSE 'Include'
END

Step 3: Use RELEVANT_DATA_FILTER as a filter on the card in Analyzer. You will want to select the value "Include" for the filter.

Step 4: Put your card on a dashboard, and then load in the controls so that users can interact with them.

If a user selects "All Dates" the card will show data for all dates. The cool thing here is that users will still be able to use the date range controls on the dashboard to control how the data is displayed (by day, month), and what range is displayed (current month, YTD, etc).

If a user selects "Selected Date" they can then select the date from which they want to show the exclude the previous 4 weeks of data from. See how I selected 3/29/2024, and my the latest date of shown in my chart is March 1st (28 days before March 29th).

Note, you can change your variables default values. For example, you can set USE_SELECTED_OR_ALL to default to "All Dates" or "Selected Dates" depending on your use case.

You could take this even further, and create another variable (DAYS_TO_EVALUATE) that would let users dynamically change the number of days from the selected date that are shown. To do this, you would simply replace the hard-coded value of 28 in the case statement with your variable, and then include that variable on your dashboard as a control.

CASE  WHEN USE_SELECTED_OR_ALL = 'Selected Date'  THEN    CASE WHEN date < = DATE_SUB(SELECTED_DATE,interval DAYS_TO_EVALUATE day)   THEN 'Include' ELSE 'Exclude' END  ELSE 'Include'END

Feel free to reach out directly via message with any questions 😁

If this answers your question, please remember to 'like' and 'accept' the post as the answer.

All comments

Prathmesh24_Diacto

There are other filters as well which I filter on with my week filter. Any other way to achieve this would also be helpful.

david_cunningham

@Prathmesh24_Diacto - I just completed this write up for a similar question. I think you'll find it useful. I've adjusted it to fit your use-case.

Step 1-1: Create a variable (SELECTED_DATE) that will be used as a control to let users select a date.

Step 2: Use the SELECTED_DATE and USE_SELECTED_OR_ALL variables in a case statement. This case statement will be used as a filter in Analyzer. I called this Beast Mode (RELEVANT_DATA_FILTER)

CASE  
  WHEN USE_SELECTED_OR_ALL = 'Selected Date'  
  THEN    
    CASE WHEN date < = DATE_SUB(SELECTED_DATE,interval 28 day) 
    THEN 'Include' 
    ELSE 'Exclude' 
  END  
  ELSE 'Include'
END

Step 3: Use RELEVANT_DATA_FILTER as a filter on the card in Analyzer. You will want to select the value "Include" for the filter.

Step 4: Put your card on a dashboard, and then load in the controls so that users can interact with them.

Note, you can change your variables default values. For example, you can set USE_SELECTED_OR_ALL to default to "All Dates" or "Selected Dates" depending on your use case.

CASE  WHEN USE_SELECTED_OR_ALL = 'Selected Date'  THEN    CASE WHEN date < = DATE_SUB(SELECTED_DATE,interval DAYS_TO_EVALUATE day)   THEN 'Include' ELSE 'Exclude' END  ELSE 'Include'END

Feel free to reach out directly via message with any questions 😁

If this answers your question, please remember to 'like' and 'accept' the post as the answer.

Prathmesh24_Diacto

@david_cunningham , Thanks a lot for the above response. I am still confused how to write a beast mode for values column (column on Y-axis). For example, if I am doing a sum of sales, what variable reference will the beast mode use?

Prathmesh24_Diacto

Thanks a lot @david_cunningham for the help!! I hope there was multi selection of reactions, so I could've selected 'Insightful', 'Like', 'Awesome'.

david_cunningham

@Prathmesh24_Diacto it was great to meet you on our call 😁 please reach out if you would like to work together on anything else. I'm always happy to collaborate!

Prathmesh24_Diacto

Absolutely, Yes!!🤝