When date range selected to 1st April to 28th april

Question & Answer
, , ,
1

Hi Team, my requirements is when date range is selected from 1st April to 28th April
then display last 7 days, even when user select the previous month then also it should display last 7 days in KPIS.

Is it possible to create such a way.

2

Hi @Alam_Syed,
A couple questions regarding your scenario..

  • When you say ‘last 7 days’, do you mean last 7 days from ‘Today’ or last 7 days prior to the period that’s picked?
  • If the date range is ever less than 7 days, do you still want to show the ‘last 7 days’?
  • When they select ‘previous month’, is it supposed to show the 7 days prior to the period month or still the same as if you’re showing current month?
  • When trying to display the last 7 days, what are you trying to display? (A sum of sales, a sum of a count…please share some additional information about the fields you are utilizing).
1 Like
3

Hi @Brett thanks for the reply,

Right, even user select the previous month the KPI card shows the within the Previous month they can see last 7 days data, we display the sum of sales.

whatever user has select the date they should see last 7 days info
ex: today current month and date is 27th April, so here when I select the date range current month it will display -7days from 27th April. so the data will be capture from 20th April to till 27th April.
similarly when I select previous month from1st March to 31th March
the data should capture 24th March to till 31th March.

Thanks
Syed

4

Hi @Alam_Syed,
Ok, thanks for the additional information! How do you have the date filter setup, is it based off a parameter or just a filter where you added the control?

My initial thoughts are that you’d want to use the addDateTime function to get the date range of 7 days prior to the max date. Then you use that to run your sum.

5

Hi @Brett

Yeah the date range is just filter without parameter.

Syed

6

Hi @Alam_Syed,
Ok, to complete this, I would start by creating parameters for your start and end date; as you’ll need parameters so that you can reference them in your calculated field. Or you could try the following route of a couple calculated fields:

  1. Create calc. fields for your 7 day window:
    month_start
    truncDate("MM", {date})
    7_Day_Window
    addDateTime(-7, 'DD', truncDate("MM", {date}))
    or try
    addDateTime(-7, 'DD', {month_start})

  2. Create a field to determine if data is within that timeframe:

ifelse(
  {date} >= addDateTime(-7, 'DD', truncDate("MM", {date}))
  AND {date} < truncDate("MM", {date}),
  1,
  0
)
  1. Sum the total of that period:
sumOver(
  ifelse(is_last_7_days = 1, {sales}, 0),
  [],
  PRE_FILTER
)

Something along these lines..Let me know if this works for your case or if you have any additional questions.

2 Likes
7

Hi @Brett thanks for the replay, let me check this

1 Like
8

Hi @Alam_Syed,
Following up here to see if you were able to test out and find the solution or if you had additional quesitons?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

9

Hi @Alam_Syed,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community.

Thank you!